March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello -
I am looking to add a measure that includes % change for the below table, that includes month over month.
I started with the following calculations but seomthing is not calculating correctly for the "total amount last month" and therefore not working downstream for the "% change".
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @amiller5. Those are the measures I'd write.
Time intelligence functions like PREVIOUSMONTH() require that you have a date table and that it's marked appropriately. Do you have one?
I do not. Is there a different function that could be used in place of it, using the current date that is in the data source?
@amiller5 it's best practice to have a date table in your data model.
Time intelligence is "syntax sugar" that makes it much easier to create these calculations. Other measures could be written, but that doesn't make up for not having one. I'd suggest reworking your data model to incorporate a date table and then this is easy peasy. You've already got the correct measures for this.
Hope this helps!
Ok - so I created a Date table.
Do I change the Total Amount last month measure to the following?
Hi,
Your measure should be:
Total amount last month = CALCULATE([total historical amount],PREVIOUSMONTH('Date'[Date]))
Hi Ashish -
I did change my measure to the one you suggested, however I nothing comes through when I put the percent change in a table.
See:
Hi,
Share the link from where i can download your PBI file. Remove the others visuals to avoid any confusion. Clearly show the problem there.
Sorry - how do I download it?
Hi,
Upload the file to Google Drive/One Drive and share the download link of that file.
The percent change visual should mimic the Opportunity stage visual - only I just want the totals visible and the percent change that pertains to month over month.
Hi,
You may download my PBI file from here.
Hope this helps.
@Ashish_Mathur I love how some things never change. What you suggested is exactly what @amiller5 thought yesterday. You are fantastic at poaching! Good job! 😡@Ashish_Mathur I'm going to make sure I tag as many Super Users and administrators on this as possible. Thanks for the help!
@amiller5 could you send a link to download a pbix from Google Drive to littlemojopuppy@gmail.com? I'll figure this out.
Hi,
Check his formula carefully. He had suggested, Total amount last month = CALCULATE([total historical amount],PREVIOUSMONTH('Date'[Month Name]))
I suggested
Total amount last month = CALCULATE([total historical amount],PREVIOUSMONTH('Date'[Date]))
Do yo not see the difference between the two? I will refrain from using cheap/derogatory language that you have.
@Ashish_Mathur why did you think your input was necessary in the first place??? THAT IS THE POINT!!! There are lots of logically equivalent ways to write measures, ranging from elegant to outright dumb.
There have been discussions in Super User meetings about respecting boundaries, not stepping on each others' toes, etc. Most people respect those boundaries. I've had instances where two of us replied to a question within seconds of each other, and I've said "that guy knows more, listen to him". I've also had instances where people I thought better than I am have deferred to me because we replied at almost the same time.
But there you go, keep poaching.
Public shaming
@Greg_Deckler
Hi @amiller5
If the date table is marked as one (right click on the table on the right hand side to check) then yes, it should be as simple as what you suggested.
Do you have a suggestion for the relationship then because the "percent change" measure doesn't flow through with the "As of Date" from Sales 4 table.
And as noted above, the As of Date no longer has a heirarchy and I need month to month, quarter over quarter. Any guidance would be greatly appreciated.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
90 | |
86 | |
76 | |
49 |
User | Count |
---|---|
166 | |
149 | |
99 | |
73 | |
57 |