cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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. 

 

vikrantarora25_0-1593516429364.png

 

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

View solution in original post

4 REPLIES 4
Highlighted
Super User I
Super User I

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

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

 




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

View solution in original post

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

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