Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm a beginner - I've been using DAX for about 4 weeks and I'm not a programmer.
I have a data set 'SharePoint' which has a list of documents published by date (Sharepoint[Created]). I can do a pivot table (on a meaasure which uses COUNTA to find the number of documents published each month) and see the number of documents published by yyyy-mm, and I've figured out DAX to get a cumulative YTD each month in my pivot table What I want to be able to do is calculate the month-over-month cumulative difference. For example if my table looks like this:
Year-Month | Month Total | Cumulative total |
2020-01 | 42 | 42 |
2020-02 | 6 | 48 |
2020-03 | 1 | 49 |
2020-04 | 27 | 76 |
I want to be able to calculate the % change March to April. It seems this should be easy to do, but I've searched and tried and nothing works. So for example, the % increase March to April would be 27/49 = 55%
Here my measure for Month total:
Solved! Go to Solution.
Hi,
Assuming the following statements are true, write this measure:
% increase = [Month total]/calculate([Month total],datesbetween(calendar[date]),date(year(min(calendar[date])),1,1),min(calendar[date])-1))
If this does not help, then share the link from where i can download your PBI file.
Hope this helps.
Hi,
Assuming the following statements are true, write this measure:
% increase = [Month total]/calculate([Month total],datesbetween(calendar[date]),date(year(min(calendar[date])),1,1),min(calendar[date])-1))
If this does not help, then share the link from where i can download your PBI file.
Hope this helps.
Thanks so much @Ashish_Mathur! You have solved my problem. I put your code with my Calendar name into daxformatter.com and this helped me understand it better. Many, many thanks for your help as I will be able to use this in other cases
You are welcome.
Hi @Anonymous,
Your formula only considers rolling on you table records based on current date.
I'd like to suggest you to extract the year and month values from your date field and use them as conditions to limit the calculation ranges to get correspond MOM cumulative result.
Regards,
Xiaoxin Sheng
Thanks @v-shex-msft
I am a beginner, can you please give me some additional detail as I do not understand your suggestion.
You want to divide the count of documents for the current month by the YTD count for the previous month.
See if that reformulation helps you.
Also - what should happen in January?
@lbendlin thanks but I'm not sure SELECTEDVALUE works (unless I don't understand your proposal). My Cumulative total is a measure. SELECTEDVALUE seems to need a column from a table?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |