topic Re: Dynamic Calculated column with a lag value of another column in DAX Commands and Tips

Dynamic Calculated column with a lag value of another column

vikrantarora25 — Tue, 30 Jun 2020 11:30:59 GMT

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

Re: Dynamic Calculated column with a lag value of another column

dedelman_clng — Fri, 28 Aug 2020 13:13:28 GMT

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

Re: Dynamic Calculated column with a lag value of another column

vikrantarora25 — Fri, 28 Aug 2020 14:42:22 GMT

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

Re: Dynamic Calculated column with a lag value of another column

dedelman_clng — Fri, 28 Aug 2020 14:47:55 GMT

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

Re: Dynamic Calculated column with a lag value of another column

AntrikshSharma — Fri, 28 Aug 2020 14:57:34 GMT

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