cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Dynamic Calculated column with a lag value of another column

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

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

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))
4 REPLIES 4
Highlighted Super User I

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

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

Proud to be a Super User!

Highlighted
Frequent Visitor

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

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))
Highlighted Super User I

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

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

Proud to be a Super User!

Highlighted Super User II

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

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

Thank you,
Antriksh Sharma

Announcements #### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge! #### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (1,437)