cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amiller5
Helper II
Helper II

Percent change - month over month, quarter over quarter

Hello - 

 

I am looking to add a measure that includes % change for the below table, that includes month over month.

amiller5_0-1659396524852.png

 

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

 

total historical amount = SUM('Sales 4'[Historical Amount])
Total amount last month = CALCULATE([total historical amount],PREVIOUSMONTH('Sales 4'[As of Date]))
percent change = DIVIDE([total historical amount]-[Total amount last month],[Total amount last month])
 
Do you have a suggestion for a different measure?
 
Thank you!
1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

17 REPLIES 17
littlemojopuppy
Super User
Super User

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

Total amount last month = CALCULATE([total historical amount],PREVIOUSMONTH('Date'[Month Name]))
My date table is the following 
Date =
VAR _calendar  = CALENDAR("1/1/2021", "12/31/2023")
RETURN
ADDCOLUMNS (
    _calendar,
    "Calendar Year", "CY " & YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
)
 
I connected the relationship and still get an error. I also want to keep my "As of date" that is in the columns as a hiearchy because I need to show the month by month as well as the quarter roll up.

Hi,

Your measure should be:

Total amount last month = CALCULATE([total historical amount],PREVIOUSMONTH('Date'[Date]))

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

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:

percent change = DIVIDE([total historical amount]-[Total amount last month],[Total amount last month])
 
I think it has something to do with the relationship between my data table: Sales 4, and my Date table. It shows one value when I put in the Date from my Date table (see the first February 2022) but I want it to calculate off the As of Date in my Datasoure table.
amiller5_0-1659569129604.png

 

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.


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

Sorry - how do I download it?

Hi,

Upload the file to Google Drive/One Drive and share the download link of that file.


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

220802_Shared file.pbix

 

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.

Untitled.png


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

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


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

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

littlemojopuppy_0-1659570003614.png

 

 

Public shaming

@Greg_Deckler

@AllisonKennedy 

@parry2k 

@mahoneypat 

@heather_Itelent

Public shaming

@Greg_Deckler

@AllisonKennedy 

@parry2k 

@mahoneypat 

@heather_Itelent

 

Good on you @Ashish_Mathur : providing solutions already offered.  😘

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.

amiller5_0-1659478958274.png

 

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.

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.