Dynamic Calculated column with a lag value of another column

[vikrantarora25]

Hi PBI Folks,

I have a table having a columns for Date, Country & Deaths plus some other columns. Each value in Deaths column represents total/cumulative deaths as on that date. 

I want to have a dymanic calculated column which is based on a slicer value and provides total deaths with a lag of X days, where X days is a value based on slicer selection. Below is the DAX formula i have used which successfully gives 7 days lag values, but instead of this fixed 7 days I want a dynamic value to be subtracted after earlier function based on a slicer selection.

I tried using whatif parameter but it does not work for a calculated column, any thoughts how I can achieve a dynamic calculated column based on slicers?

Deaths 7 Days Ago = MAXX(FILTER('Main Tbl','Main Tbl'[Country/Region]=EARLIER('Main Tbl'[Country/Region]) &&
'Main Tbl'[Date]=EARLIER('Main Tbl'[Date])-7),
'Main Tbl'[Deaths])

This is my table, let me know if more info is needed to solve this. 

Regards

Vikrant

[dedelman_clng]

Hi @vikrantarora25 -

First off, you cannot do this as a calculated column as those do not re-calculate when a slicer or filter is applied. You want to code this as a measure.

That being said, you can access the value selected on a slicer with SELECTEDVALUE(Slicer[Column]), making your code something like this

Deaths 7 Days Ago =
VAR __Lag =
    SELECTEDVALUE ( Slicer[Column] )
RETURN
    MAXX (
        FILTER (
            'Main Tbl',
            'Main Tbl'[Country/Region] = EARLIER ( 'Main Tbl'[Country/Region] )
                && 'Main Tbl'[Date]
                    = EARLIER ( 'Main Tbl'[Date] ) - __Lag
        ),
        'Main Tbl'[Deaths]
    )

To then make this a "column", just add it along with the other pertinent data from Main Tbl into a table visual.

Hope this helps

David

[vikrantarora25 · Accepted solution]

Thanks David, actually posted this more than a month back & was able to acheive it in a different way. I appreciate your effort to answer a ques so long down in the queue 🙂

CFR (Deaths/Total Cases X Days Ago) = SWITCH(SELECTEDVALUE('Days Lag Tbl'[Lag Days]),

7,IFERROR(DIVIDE(CALCULATE(SUM('Main Tbl'[Cumulative Deaths])),CALCULATE(SUM('Main Tbl'[Cumulative Cases]),DATEADD('Main Tbl'[Date],-7,DAY))),0),

14, IFERROR(DIVIDE(CALCULATE(SUM('Main Tbl'[Cumulative Deaths])),CALCULATE(SUM('Main Tbl'[Cumulative Cases]),DATEADD('Main Tbl'[Date],-14,DAY))),0),

21,IFERROR(DIVIDE(CALCULATE(SUM('Main Tbl'[Cumulative Deaths])),CALCULATE(SUM('Main Tbl'[Cumulative Cases]),DATEADD('Main Tbl'[Date],-21,DAY))),0),

30,IFERROR(DIVIDE(CALCULATE(SUM('Main Tbl'[Cumulative Deaths])),CALCULATE(SUM('Main Tbl'[Cumulative Cases]),DATEADD('Main Tbl'[Date],-30,DAY))),0))

[AntrikshSharma]

@vikrantarora25  You can remove IFERROR because DIVIDE will take care of it, also instead of using SUM () multiple times, create a separate measure for it, hence increasing readability :

Total Deaths =
SUM ( 'Main Tbl'[Cumulative Cases] )

CFR (Deaths/Total Cases X Days Ago) =
SWITCH (
    SELECTEDVALUE ( 'Days Lag Tbl'[Lag Days] ),
    7,
        DIVIDE (
            [Total Deaths],
            CALCULATE ( [Total Deaths], DATEADD ( 'Main Tbl'[Date], -7, DAY ) )
        ),
    14,
        DIVIDE (
            [Total Deaths],
            CALCULATE ( [Total Deaths], DATEADD ( 'Main Tbl'[Date], -14, DAY ) )
        ),
    21,
        DIVIDE (
            [Total Deaths],
            CALCULATE ( [Total Deaths], DATEADD ( 'Main Tbl'[Date], -21, DAY ) )
        ),
    30,
        DIVIDE (
            [Total Deaths],
            CALCULATE ( [Total Deaths], DATEADD ( 'Main Tbl'[Date], -30, DAY ) )
        )
)

[dedelman_clng]

Glad to know you got it working.

One additional point of interest - DIVIDE() has its own built-in error handling, so you should not need IFERROR

DIVIDE( Numerator, Denominator, AlternateResult [if Denominator is 0]  )

David