Month Percent Change

[amandabus21]

Hello, 

Im looking to create this type of a month percent change based on the Previous Year.

I have recreated the same values in Power BI using a measure but am missing the percent change: 

This is the measure to get the values: 

Accidents Per 100K Miles = DIVIDE('Datamart AccidentSummary'[Sum of PV],'Datamart AccidentSummary'[Sum of Mileage]) *100000 

Help would be greatly appreciated!

[amitchandak · Accepted solution]

@amandabus21 , you have create measures like this month vs last year same month and use

measure like these can help

This month = CALCULATE([Net],DATESMTD(ENDOFMONTH('Date'[Date])))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))


last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the above structure, you can use insinscope and replace grand total with diff%

if(Isinscope(Table[Year]), [This Month], [Diff])

or check Hybrid Display

if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

[Anonymous · Accepted solution]

Hi @amandabus21 ,

According to your description, you want to create this type of a month percent change based on the Previous Year? Here are my steps you can follow as a solution.

(1)This is my test data.

(2)We can create  a measure.

test = IF( HASONEVALUE('Datamart AccidentSummary'[Fiscal_Year]) ,[Accidents Per 100K Miles]  , FORMAT( DIVIDE( CALCULATE([Accidents Per 100K Miles],'Datamart AccidentSummary'[Fiscal_Year]=2021)-

   CALCULATE([Accidents Per 100K Miles],'Datamart AccidentSummary'[Fiscal_Year]=2022) , CALCULATE([Accidents Per 100K Miles],'Datamart AccidentSummary'[Fiscal_Year]=2022) ) , "0.0%")

    )

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak · Accepted solution]

@amandabus21 , you have create measures like this month vs last year same month and use

measure like these can help

This month = CALCULATE([Net],DATESMTD(ENDOFMONTH('Date'[Date])))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))


last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the above structure, you can use insinscope and replace grand total with diff%

if(Isinscope(Table[Year]), [This Month], [Diff])

or check Hybrid Display

if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc