Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

How to add calculated column for matrix with dinamic dates

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

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,


    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.

2 Replies