How to Calculate % Increase/Decrease from trailing months?

[Anonymous]

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.

Thanks in advance!!

[Anonymous]

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.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@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
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Anonymous]

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

[amitchandak]

@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