cancel
Showing results for
Did you mean:

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.

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.

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
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
17 REPLIES 17
Community Champion

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?

Helper II

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?

Community Champion

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

Helper II

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
_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.
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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.

Super User

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
Helper II

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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.

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Champion

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

Super User

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
Community Champion

@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

@heather_Itelent

Community Champion

Public shaming

@Greg_Deckler

@heather_Itelent

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

Community Champion

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.

Helper II

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors