How to add calculated column for matrix with dinamic dates

[Triandh]

Hi, Guys!

I'm trying to add a calculated column for matrix visual.
I have dynamic dates in column, i.e if we select particular date in slicer, the matrix will how the laste five days of selected date.

Rn I need to show last column as (Last date - last date-1).
In place of column subtotal, I need value of (03/20/24 - 03/19/2024) i.e (95-44) = 51 instead of total as 153. 

Does anyone have passed by this before? Or have any idea how to solve this problem? 

Thanks, 

Regards

[Anonymous · Accepted solution]

Hi @Triandh ,

I am not sure how your source data table is designed, here is my sample.
The test data is as follows.

Creates a measure to calculate the difference between the last day and the day before.

Custom Total = 
VAR a = CALCULATE(SUM('Table'[Amount]),'Table'[Date]=MAX('Table'[Date]))   ----Value for the last day
VAR b = CALCULATE(SUM('Table'[Amount]),'Table'[Date]=MAX('Table'[Date])-1) ----Value of the day before the last date
RETURN
a-b

After adding the measure to the matrix, go to the matrix's formatting settings and disable text wrap in Values and text wrap in Column Headers.

Hide the measure headers under all dates except the last date.

The final result is as follows, hopefully it will meet your needs.

Please see the attahced pbix for reference.

Best Regards,
Dengliang Li

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

[Greg_Deckler]

@Triandh See if these help. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Also this but with SUMX versus AVERAGEX and obviously day instead of months. https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Better-Rolling-Average/m-p/2897874#...