Forum Discussion

AnF0's avatar
AnF0
New Member
1 year ago
Solved

Calculate Percentage Change With a Relative Date Slicer

I'm looking for a way to have a card visual show a percentage change but have it adjust with a relative date slicer applied to the page. I have seen previous measures for date range slicers but have ...
  • danextian's avatar
    1 year ago

    Hi AnF0 

     

    Create a numeric range parameter table. Assuming the name of the table created is LastXDays, the name of the automatically crate measure is also the same, create these measures. 

    Last X Days = 
    VAR _MaxDate =
        CALCULATE ( MAX ( Dates[Date] ), ALLSELECTED ( Dates ) )
    VAR _StartDate = _MaxDate - [LastXDays Value] + 1
    RETURN
        CALCULATE (
            [Total Revenue],
            KEEPFILTERS ( Dates[Date] >= _StartDate && Dates[Date] <= _MaxDate )
        )
    
    Last X Days Prior = 
    VAR _MaxDate =
        CALCULATE ( MAX ( Dates[Date] ), ALLSELECTED ( Dates ) )
    VAR _EndDate = _MaxDate - [LastXDays Value] - 1
    VAR _StartDate = _EndDate - [LastXDays Value] + 1
    RETURN
        CALCULATE (
            [Total Revenue],
            KEEPFILTERS ( Dates[Date] >= _StartDate && Dates[Date] <= _EndDate )
        )
    
    Last X Days Variance = 
    [Last X Days] - [Last X Days Prior]
    
    Last X Days Delta = 
    DIVIDE ( [Last X Days Variance], [Last X Days Prior] )
    

    Note: In the above example, the _MaxDate  is is the max of all the visible Dates[Date] rows. Please see the attached sample pbix.