Cumulative Month

[Chloe19]

Hi 

Can someone help me with the formula for cumulative but only for:

Cumulative for THIS MONTH

then separate formula for:

Cumulative for LAST MONTH

then separate formula for:

Cumulative for 2 MONTHS AGO

I have a variable already summed up $(vSUM_OF_X) and a date field [Effective Date].

Thanks

[Vikash]

Hi Cloe,

You can achieve this by using set expression. Try the below expressions-

For This Month-
sum({$<Date={">=$(=num(monthstart(max(Date)))) <=$(=num(monthend(max(Date))))"}>}Sales)

For Last Month - 

sum({$<Date={">=$(=num(monthstart(max(Date),-1))) <=$(=num(monthend(max(Date),-1)))"}>}Sales)

For 2nd Last Month -

sum({$<Date={">=$(=num(monthstart(max(Date),-2))) <=$(=num(monthend(max(Date),-2)))"}>}Sales)

Note:- Use your date dimensions and Measures. like- For Date, use [Effective Date]..etc.

Regards,

VK

[Chloe19]

Hi VK

Thanks for the response.

Tried it, while the expression says its ok there is no problem, I do get a result of 0 for the current month and for the 2 months ago just returns the total sum all months, not cumulative for the month which is 2 months ago.

sum({$<Date={">=$(=num(monthstart(max([Effective Date]),-2))) <=$(=num(monthend(max([Effective Date]),-2)))"}>}TOTAL_CASH)

[Vikash]

Hi Cloe,

You need to check the date value from your date field. Check below -

sum({$<[Effective Date]={">=$(=num(monthstart(max([Effective Date]),-2))) <=$(=num(monthend(max([Effective Date]),-2)))"}>}TOTAL_CASH)

Try the above expression, I think it should work now. 

Regards,

VK

[Chloe19]

Hi VK

Ah I see I missed the date replacement there, however still the same issue, still get 0

Chloe

[Vikash]

Hi Cloe,

The same expression works for me. There must be some Date type of issue.

Could you please let me know the format of your [Effective Date] field?

VK

[Chloe19]

Hi

The filed is DD/MM/YYYY

What I am trying to do is this formula from POWER BI: 

Cummulative Cash = CALCULATE([Total Cash],FILTER(ALLSELECTED('Date'),'Date'[Date] <= MAX('Date'[Date])))

So the result would look like the attached screenshot out of POWER BI

Chloe

[Radford85]

Means it takes the previous months values too. Ex; if I select Feb month means it shows the cumulative sum of Jan+Feb. Similarly for Mar = JanFebMar. Here in this calculation right now I using Order Date but I want to calculate the same formula based on Fiscal month .

Kroger Feed

[Chloe19]

So the below works for cumulative 

=RangeSum(Above(TOTAL Sum(TOTAL_CASH), 0, RowNo(TOTAL)))

However am trying to write a cumulative for previous month and month before that.

So then no matter what month I filter on it will take into account the previous month and the month before that.

Example in  the attached, even though I filtered date to be FEB only, I still have the cumulative for the previous 2 months.