'PowerBI Last N Months Trend Line - Ignore All Date Filters
Have a simple, 2 table model: [Sales]*-[Calendar] with sales that span from 1/1/2021 to yesterday. Day grain. Calendar is a standard date dimension.
Need: I want a line chart that shows the sales trend for each day since the first of the month, 3 months back, to yesterday.
I want this line chart measure to ignore the influence from all Calendar[Date] slicers/filter that happen to be on the report.
I understand I can use the "edit interactions" feature and stop the Date slicers from affecting the trend line - and that works - with this code.
However, I want to learn how to ignore these external filter affects within DAX & not rely on using the edit-interaction feature.
I've gotten this far. This code calcs the measure I want, but a date slicer on the report affects it and I don't want it to. What am I missing?
Total $ Last 3 Months Trend =
VAR __monthsBack = 3
VAR __endDate =
CALCULATE ( MAX ( Sales[SalesDate] ), ALL ( 'Calendar' ) )
VAR __endDateMinus_NMonths =
EDATE ( __endDate, - __monthsBack )
VAR __firstDate =
DATE ( YEAR ( __endDateMinus_NMonths ), MONTH ( __endDateMinus_NMonths ), 01 )
RETURN
IF (
MAX ( 'Calendar'[Date] ) <= __endDate
&& MAX ( 'Calendar'[Date] ) >= __firstDate,
CALCULATE (
[Total $],
FILTER (
'Calendar',
'Calendar'[Date] >= __firstDate
&& 'Calendar'[Date] <= __endDate
),
REMOVEFILTERS ( 'Calendar'[Date] )
),
BLANK ()
)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
