Percent change - month over month, quarter over quarter

[amiller5]

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

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!

[littlemojopuppy]

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?

[amiller5]

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?

[littlemojopuppy]

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

[amiller5]

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.

[Ashish_Mathur]

Hi,

Your measure should be:

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

[amiller5]

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.

[Ashish_Mathur]

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.

[amiller5]

Sorry - how do I download it?

[Ashish_Mathur]

Hi,

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

[amiller5]

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.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[littlemojopuppy]

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

[Ashish_Mathur]

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.

[littlemojopuppy]

@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

@AllisonKennedy 

@parry2k 

@mahoneypat 

@heather_Itelent

[littlemojopuppy]

Public shaming

@Greg_Deckler

@AllisonKennedy 

@parry2k 

@mahoneypat 

@heather_Itelent

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

[littlemojopuppy]

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.

[amiller5]

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.