Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate difference month-to-month in cumulative values

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-MonthMonth TotalCumulative total
2020-014242
2020-02648
2020-03149
2020-042776

 

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:

Month total =
CALCULATE(
COUNTA(SharePoint[Name]
)
)
 
"Sharepoint[Created]" is a date field and I have a relationship between that and a universal Dates table
Here is my Cumulative total:
Cumulative total =
CALCULATE(
COUNTA(SharePoint[Name]),
FILTER(
ALLSELECTED('SharePoint'),
SharePoint[Created] <= max (SharePoint[Created])
)
)
 
This gives the wrong answer:
Increase % = DIVIDE( [Month total] , [cumulative total] )
 
I appreciate any ideas!
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Assuming the following statements are true, write this measure:

  1. Month Total is a measure
  2. Cumulative total is a measure
  3. There is a Calendar Table
  4. There is a relationship between the Created column and the Date column of the Calendar Table
  5. To your visual, you have dragged Year and Month from the Calendar Table

% 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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Assuming the following statements are true, write this measure:

  1. Month Total is a measure
  2. Cumulative total is a measure
  3. There is a Calendar Table
  4. There is a relationship between the Created column and the Date column of the Calendar Table
  5. To your visual, you have dragged Year and Month from the Calendar Table

% 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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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 

 

% increase =
[Month total]
/ CALCULATE (
[Month total],
DATESBETWEEN (
Dates[Date],
DATE ( YEAR ( MIN ( Dates[Date] ) ), 1, 1 ),
MIN ( Dates[Date] ) - 1
)
)

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Thanks @v-shex-msft 

I am a beginner, can you please give me some additional detail as I do not understand your suggestion.

lbendlin
Super User
Super User

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_0-1596675024474.png

 

Anonymous
Not applicable

@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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.