Wrong total by month DAX.

[ROG]

Hi all,

My table below is showing the number of Subscribers by week, and as the report refreshes once a week (on Tuesdays), each week the report adds up the new subscribers.

The problem is when I add a filter on the report's page to see the total by month, because the report sums up the total amount of each week, instead of only add the new amount from the last week.

Here are the measures I'm using.

NoSubs = CALCULATE ( [Subscribers], data_history_append_update[FLAG] = "Opt-In" )

Subscribers = SUM (data_history_append_update[SUB] )

I exported it to excel for a better understanding of the problem.

Column B is the correct amount for each week, but with the wrong total.

Column C shows how many new subscribers we have acquired by week, and with the correct total.

So, could you please help me with a DAX, that gives me the correct amount by month, and that keeps the correct amount by week?

Unfortunatelly, in this case, I do not have a date table. I have a date column, and Run_date which gives me the day the report was updated.

 Thank you very much!🙂

[Anonymous]

Hi @ROG 

Can you provide a whole data for me ? From this information you have provided so far, there is no way for me to provide you with a perfect solution. You need to provide the information you have so far and the final result you want to achieve .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @ROG 

727291 is the sum of column Opt-Ins . You can create a measure like this :

Measure = 
SUMX(FILTER(ALL('Table'),
'Table'[Week Start Date]<=MAX('Table'[Week Start Date])&&'Table'[Country]=MAX('Table'[Country])),[Weekgrowth])

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ROG]

Hi @Anonymous  thanks very much for your reply I believe it will work, but I do not have the 'WeekGrowth' column on my model. That column on the screenshot was just to explain what I need. 

Can you please tell me how I would get the week change? I do not have a date table, a column.

Also, on your measure how would you add the filter I have on mine (OptinFlag)?

Many thanks!

[amitchandak]

@ROG , measure seem fine for grand total, only change I can suggest as of now

NoSubs = CALCULATE ( [Subscribers], filter(data_history_append_update, data_history_append_update[FLAG] = "Opt-In" ) )