cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## How to Calculate % Increase/Decrease from trailing months?

Hi All,

I am actually quite new to Power BI. It would be really helpful if you could solve my requirement. My requirement is, I have a Month Field and Total Units that got registered on every month.  I dont have date field in my data. Using the Registration month field, I need to create a measure (Percentage Change) by using the following formula,

((This month - Previous month)/Previous Month) * 100 .

My data will be,

 Registration Month Total Units Percentage Change 1 141652 2 63932 -54.8668568 3 84667 32.43289745 4 90834 7.283829591 5 85944 -5.383446727 6 124071 44.36260821 7 102398 -17.46822384 8 174083 70.00625012 9 128203 -26.35524434 10 121915 -4.904721418 11 125734 3.132510356 12 116228 -7.5604053

I need the DAX formula to create the Percentage Change measure.

Any pointers on this would be really helpful.

1 ACCEPTED SOLUTION
Super User

@Anonymous , if you have date in the data , you can use time intelligence , else you can use a separate period table , prefer year period table and join it with you tbale back and use rank for this

example

with date

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

New table and period rank as column

Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)

measures
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

4 REPLIES 4
Community Support

Hi @Anonymous,

Can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User

@Anonymous , if you have date in the data , you can use time intelligence , else you can use a separate period table , prefer year period table and join it with you tbale back and use rank for this

example

with date

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

New table and period rank as column

Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)

measures
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

Anonymous
Not applicable

@amitchandak , I dont have date in my data. And can you ellaborate what is the new table I need to create & how to join in detail please.

Super User

@Anonymous , if you have a year period, Create a new column if not present

Year period = [Year] * 100 +[period]

Now create a new table taking distinct or summarize Year, period and year period

Date = summarize(Table, Table[year], Table[period], Table[Year Period])

Join on year period

In new table ceate Rank and create measures as suggested