Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Year | Month | This Month | Last Month | Monthly Variance |
2019 | Jan | 19,223,321 | 18,290,500 | 932,821 |
2019 | Feb | 19,651,173 | 19,223,321 | 427,852 |
2019 | Mar | 19,891,796 | 19,651,173 | 240,623 |
Current Total | 40,475,790 | |||
Desired Total | 1,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
Solved! Go to Solution.
@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 ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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 ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |