Forum Discussion
JimJim
4 years agoResponsive Resident
Dynamic date filter not working
Hi Guys, I have attached a link to a pbix which includes a month slicer and a matrix, I would like to select one month from the slicer and have the matrix display only 3 months of data up to and ...
JimJim
4 years agoResponsive Resident
I have also tried to use a days instead of whole months but this also does not return the expected results:
Weirdly, when I replace the __days variable with a literal, such as -91, it works, but as soon as I pass in the __days variable it stops working again
Quote Count R3M =
// get the selected date
VAR __selectedDate =
MAX ( 'Time'[Date] )
// get the financial key for selected date - 2 months
VAR __financialPeriod =
MIN ( 'Time'[YearMonthKey] ) - 2
// get the earliest date for __financialPeriod
VAR __fromDate =
CALCULATE (
MIN ( 'Previous Time'[Date] ),
'Previous Time'[YearMonthKey] = __financialPeriod )
// calculate number of days
VAR __days = (DATEDIFF(__fromDate, __selectedDate, DAY) + 1 ) * -1
// create a date table
VAR __dates =
DATESINPERIOD('Previous Time'[date],
__selectedDate,
__days,
DAY)
VAR __result =
CALCULATE([Quote Count],
REMOVEFILTERS('time'),
KEEPFILTERS(__dates),
USERELATIONSHIP('Previous Time'[date], 'time'[date]))
RETURN
__result