Cumulative per Month

Anything related to PowerPivot and DAX Formuale
okat
Posts: 16
Joined: Tue Apr 18, 2017 4:04 am

Cumulative per Month

Postby okat » Fri Dec 15, 2017 1:01 am

Hi Matt. I have a sales file. I need to get the cumulative per month of the difference between the current month and the previous month. To this end I implemented the following measures:

1-Total sales = SUM (sales)

2-Total sales Month Previous = CALCULATE ([total sales]; PREVIOUSMONTH (' Calendar ' [Date]))

3-Different = [Total sales]-[total sales Month Previous]

4-= Cumulative per Month =

CALCULATE ([Difer];
FILTER(
ALL('Calendar');
'Calendar'[Date]<=MAX('Calendar'[Date])&&
'Calendar'[Month]=MAX('Calendar'[Month])
)
)


I can't find a measure to do this. For example, if I am in January 2014, I want to accumulate the differences of January of 2012 + January of 2013 + January of 2014.
Thank you so much for your help.
Attachment file

[http://jmp.sh/FOqopTC]
Attachments
Example.xlsx
(257.79 KiB) Downloaded 104 times

MattAllington
Posts: 993
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Cumulative per Month

Postby MattAllington » Tue Jan 02, 2018 11:59 am

So you want to use formula 4, and then apply that for 3 prior years? How should the formula know how many prior years? eg why not 5 prior years, or 2?
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests

cron