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.
techies
Super User
1 year agoHi AnF0 please try this for the previous period measure
SelectedDays =
DATEDIFF(MIN('Date'[Date]), MAX('Date'[Date]), DAY) + 1
Cases_PreviousPeriod =
VAR MinDateVisible = MIN('Date'[Date])
VAR DaysSelected = [SelectedDays]
VAR PrevPeriod =
DATESINPERIOD('Date'[Date], MinDateVisible - 1, -DaysSelected, DAY)
RETURN
CALCULATE(
COUNTROWS('cases'),
REMOVEFILTERS('Date'),
FILTER(
ALL('Date'),
'Date'[Date] IN PrevPeriod
)
)