Need Date Diff % in the Matrix table

[Anonymous]

 Hi Everyone,

I have Matrix table like below

Month  2021 2020

1           10     20

2            30    40

3            50    60

Now i need to calculate Diff%Column in Matrix.I couldn't place measure in Columns section of Matrix table.

Output Should be:

Month  2021 2020 DateDiff%

1           10     20     50%

2            30    40     30%

3            50    60     30%


Thanks in Advance

[amitchandak]

@Anonymous , You need to have three measures. Year can not be a column and then you get diff

example 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

or

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 ])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series

Hybrid table is one possibility

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/1354591
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428

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

[Anonymous]

Hi @amitchandak 

Thanks for your reply.

Requirement for me is to have the Year as column name and to find the diff% for every month.

We don't have any other option to get like that?

thanks.

[v-kkf-msft]

Hi @Anonymous ,

Create a new table:

Then try the following measure:

Measure = 
SWITCH(
    MAX('Matrix Columns'[Column]),
    "2020", SUM('Table'[Value]),
    "2021", SUM('Table'[Value]),
    "DateDiff%", FORMAT(
                    1 - DIVIDE(
                        CALCULATE(SUM('Table'[Value]), 'Matrix Columns'[Column] = "2021"),
                        CALCULATE(SUM('Table'[Value]), 'Matrix Columns'[Column] = "2020")
                        ),
                    "0.00%"
                )
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

Hi @v-kkf-msft 

Thanks for your reply.

Can this measure be dynamic,it means if i move to 2022 i need to have 2021 and 2022 in my table as column names?

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

In this case, I think it is easier to display DateDiff% in the column total.

Modify the name of the Column subtotals to "DateDiff%".

And then create the following measure:

Measure = 
var Max_Year = MAXX( ALLSELECTED('Table'), 'Table'[Year] ) 
var Min_Year = MINX( ALLSELECTED('Table'), 'Table'[Year] ) 
return
    IF(
        HASONEVALUE('Table'[Year]),
        SUM('Table'[Value]),
        FORMAT( 1 - DIVIDE(
                        CALCULATE( SUM('Table'[Value]), 'Table'[Year] = Max_Year ),
                        CALCULATE( SUM('Table'[Value]), 'Table'[Year] = Min_Year )
                    ),
                "0.00%"
        )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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