Calculate % Change By Month for Multiple Categories

[QuinnG]

Hi,

I have an opportunity tracker where I'm trying to calculate the percentage change in the number of opportunities from month to month. My table looks like this where I have the month and I've calculated the total number of opportunities per category by month and I want to generate the % Change column. Thanks in advance for any help.

Year	Month	Quarter	Categories	Total Hours	Number of Opportunities	% Change
2022	4	Q2	A	19	3	0
2022	4	Q2	B	20	4	0
2022	4	Q2	C	15	5	0
2022	4	Q2	D	10	6	0
2022	4	Q2	E	5	8	0
2022	5	Q2	A	7	2	-33%
2022	5	Q2	B	10	3	-25%
2022	5	Q2	C	20	4	-20%

[v-shex-msft · Accepted solution]

HI @QuinnG,

I'd like to suggest you add a custom column to merge year month field values, then you can use it as a condition to calculate the '% changes' based on category:

calculate column:

YearMonth =
Table[Year] * 100 + Table[Month]

Measure:

formula =
VAR currYearMonth =
    MAX ( Table[YearMonth] )
VAR prevYearMonth =
    CALCULATE (
        MAX ( Table[YearMonth] ),
        FILTER ( ALLSELECTED ( Table ), [YearMonth] < currYearMonth ),
        VALUES ( Table[Category] )
    )
VAR currOpp =
    MAX ( Table[Number of Opportunities] )
VAR prevOpp =
    CALCULATE (
        MAX ( Table[Number of Opportunities] ),
        FILTER ( ALLSELECTED ( Table ), [YearMonth] = prevYearMonth ),
        VALUES ( Table[Category] )
    )
RETURN
    DIVIDE ( currOpp - prevOpp, prevOpp )

Regards,

Xiaoxin Sheng

[amitchandak]

@QuinnG , Create a date from month and year, if you do not have a date

Date = Date([Year], [Month],1)

Try measures like

Join this date with date of a date table, and use column from that date table in visual

MTD = CALCULATE(AverageX(values('Date'[Date]), calculate(SUM(Table[Qunatity Produced])) ),DATESMTD('Date'[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]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))

with help from column month Rank

Mesures

Month Rank = RANKX(all('Date'),'Date'[Year Month],,ASC,Dense)

Measure
This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month 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

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...