Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
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
Hi VK
Ah I see I missed the date replacement there, however still the same issue, still get 0
Chloe
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
Hi
The filed is DD/MM/YYYY
What I am trying to do is this formula from POWER BI:
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 .
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.