Calculate % change with date hierarchy

[Anonymous]

Hi everyone, 

I want to calculate the change in cost in percentage based on a changeable period. I am using Matrix visaulization with date hierarchy.

For example,

This is my data,

In Year level: 

Year	Product A	Product B
2019	100	500
2020	120	130
2021	110	130

Drill down in Quarter level:

Year	Product A	Product B
2019
Q1	50	200
Q2	50	300
2020
Q2	120	130
2021
Q1	110	130

I would like the table to show:

Year	Product A	% Change	Product B	% Change
2019	100		500
2020	120	(120-100)/100= 20%	130	(130-500)/500 = -74%
2021	110	(110-120)/120= -8.3%	130	(130-130)/130=0%

Same after drill down to next level:

Year	Product A	% Change	Product B	% Change
2019
Q1	50		200
Q2	50	0	300	50%
2020
Q2	120	0	130	0
2021
Q1	110	0	130	0

Could someone help me with this? 

I'd like to know if this is possible to do in Power BI, and how to do it.

Thank you.

[v-yalanwu-msft]

Hi, @Anonymous ;

You could create a measure as follows:

change =
VAR _yearlast =
    CALCULATE (
        SUM ( [value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Product] ),
            YEAR ( [Date] )
                = YEAR ( MAX ( [Date] ) ) - 1
        )
    )
VAR _year =
    CALCULATE (
        SUM ( [value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Product] ),
            YEAR ( [Date] ) = YEAR ( MAX ( [Date] ) )
        )
    )
VAR _qualast =
    IF (
        QUARTER ( MAX ( [Date] ) ) = 1,
        CALCULATE (
            SUM ( [value] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Product] ),
                YEAR ( [Date] )
                    = YEAR ( MAX ( [Date] ) ) - 1
                    && QUARTER ( [Date] ) = 4
            )
        ),
        CALCULATE (
            SUM ( [value] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Product] ),
                YEAR ( [Date] ) = YEAR ( MAX ( [Date] ) )
                    && QUARTER ( [Date] )
                        = QUARTER ( MAX ( [Date] ) ) - 1
            )
        )
    )
VAR _quar =
    CALCULATE (
        SUM ( [value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Product] ),
            YEAR ( [Date] ) = YEAR ( MAX ( [Date] ) )
                && QUARTER ( [Date] ) = QUARTER ( MAX ( [Date] ) )
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Date].[Year] )
            && NOT ( ISINSCOPE ( 'Table'[Date].[Quarter] ) ),
        DIVIDE ( _year - _yearlast, _yearlast ),
        IF (
            ISINSCOPE ( 'Table'[Date].[Quarter] ),
            DIVIDE ( _quar - _qualast, _qualast )
        )
    )

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yalanwu-msft, 

Thank you so much for helping out.

I tried this in my dashboard. However, I use DirectQuery to get data and it seems like ISINSCOPE function is not supported for use in DirectQuery mode. I also created date hierarchy manually because of DirectQuery mode.

Do you know if there's any other ways to solve this problem?

Again, thank you for your suggestion.

Best, 

Jenny

[v-yalanwu-msft]

HI, @Anonymous ;

If it's DirectQuery , the recommendation is to import mode or create a virtual table using Summarize ();

https://docs.microsoft.com/en-us/dax/summarize-function-dax

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yalanwu-msft  ,

Thank you so much for providing this solution!!
I tried the code and the pbix file you sent me.

I did the same measurement with Month level in date hierarchy; however, it is working as well as quarter and year level.

I added:

change = var _yearlast=CALCULATE(SUM([value]),FILTER(ALLEXCEPT('Table','Table'[Product]),YEAR([Date])=YEAR(MAX([Date]))-1))

var _year=CALCULATE(SUM([value]),FILTER(ALLEXCEPT('Table','Table'[Product]),YEAR([Date])=YEAR(MAX([Date]))))

var _qualast=IF(QUARTER(MAX([Date]))=1,CALCULATE(SUM([value]),FILTER(ALLEXCEPT('Table','Table'[Product]),YEAR([Date])=YEAR(MAX([Date]))-1&&QUARTER([Date])=4)),CALCULATE(SUM([value]),FILTER(ALLEXCEPT('Table','Table'[Product]),YEAR([Date])=YEAR(MAX([Date]))&&QUARTER([Date])=QUARTER(MAX([Date]))-1)))

var _quar=CALCULATE(SUM([value]),FILTER(ALLEXCEPT('Table','Table'[Product]),YEAR([Date])=YEAR(MAX([Date]))&&QUARTER([Date])=QUARTER(MAX([Date]))))

var _month=CALCULATE(

 SUM('Table'[value]),

 FILTER(

 ALLEXCEPT('Table','Table'[Product]),

 AND(YEAR([Date])=YEAR(MAX([Date])), MONTH([Date])=MONTH(MAX([Date])))

 )

)

var _monlast=IF(

 MONTH(MAX([Date]))=1,

 CALCULATE(

 SUM('Table'[value]),

 FILTER(ALLEXCEPT('Table','Table'[Product]), YEAR([Date])=YEAR(MAX([Date]))-1&&QUARTER([Date])=QUARTER(MAX([Date]))-1&&MONTH([Date])= MONTH(MAX([Date])=12))

 ),

 CALCULATE(

 SUM([value]),

 FILTER(ALLEXCEPT('Table','Table'[Product]),YEAR([Date])=YEAR(MAX([Date]))&&QUARTER([Date])=QUARTER(MAX([Date]))&&MONTH([Date])=MONTH(MAX([Date]))-1)

 )

 )

return 

SWITCH(

 TRUE(),

ISINSCOPE('Table'[Date].[Year])&&NOT(ISINSCOPE('Table'[Date].[Quarter])),DIVIDE(_year-_yearlast,_yearlast),

ISINSCOPE('Table'[Date].[Quarter])&& NOT(ISINSCOPE('Table'[Date].[Month])),DIVIDE(_quar-_qualast,_qualast),

ISINSCOPE('Table'[Date].[Month]),DIVIDE(_month-_monlast, _month))

However, The matrix shows 100% when there's no previous value, is there anything wrong with my code and logic?
I would like to make it blank instead of showing 100%.

I was trying to approach the percentage change number by getting the previous date instead of using -1 o get last month/quarter/year value, because some of my date are discontinuously...

Do you have any suggestion on this idea?

Thanks again!!

[amitchandak]

@Anonymous , I think Qtr level data should be sufficient.

You need to create QOQ and YOU different and show that at that level, I think is in scope will help

if(isincope('Date'[Year]) && Not(isinscope('Date'[Qtr])) , [YOY%] , [QOQ%] )

If you do have date , Create a table with Year, Qtr and YearQtr [YYYYQ] , new tbale say date

and add rank column

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

Measures
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0