Forum Discussion
AnF0
1 year agoNew Member
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 ...
- 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.
danextian
Super User
1 year agoHi 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.