cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hyggins Frequent Visitor
Frequent Visitor

Incorrect Measure Total - subtracting between two Month over Month measures

Hello,

 

I'm trying to get my Total Changes measure to calculate correctly. I'm using a matrix visual. Red is what I get currently, Green is the desired total.

YearMonthThis MonthLast MonthMonthly Variance
2019Jan19,223,32118,290,500932,821
2019Feb19,651,17319,223,321427,852
2019Mar19,891,79619,651,173240,623
   Current Total40,475,790
   Desired Total1,601,296

 

 

Measures:
This Month = SUM(Table[Widgets])
Last Month = CALCULATE([This Month], PREVIOUSMONTH(DimDate[Date]))
Monthly Variance = [This Month] - [Last Month]

Final Measure = IF(HASONEVALUE(DimDate[Year]) && HASONEVALUE(DimDate[Month])
                        ,[Monthly Variance]
                        ,?? // this line is what I'm trying to solve

                       )

 

I've been scouring the forums all day and can't seem to get this to work. I've tried variations using SUMX and SUMMARIZE but have yet to be successful.

 

Any assistance is very much appreciated! Thank you!

 

Grant

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Incorrect Measure Total - subtracting between two Month over Month measures

@hyggins try to change your measure like below and then it should work and total of your monthly variance will be correct.

 

Last Month = 
CALCULATE(
[This Month],
DATEADD( DimDate[Date], -1, MONTH )
)





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

2 REPLIES 2
Super User IV
Super User IV

Re: Incorrect Measure Total - subtracting between two Month over Month measures

@hyggins try to change your measure like below and then it should work and total of your monthly variance will be correct.

 

Last Month = 
CALCULATE(
[This Month],
DATEADD( DimDate[Date], -1, MONTH )
)





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
hyggins Frequent Visitor
Frequent Visitor

Re: Incorrect Measure Total - subtracting between two Month over Month measures

@parry2k Wow! That did it! Thank you so much!

 

I'm going to have to dig into the nuances of PREVIOUSMONTH vs DATEADD to see what the differences are. 

 

Thanks again!

 

Grant

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors