Forum Discussion
Filtered visuals show all months when using +0
I have a 13 month trends line chart and matrix, however when I attempt to include results with zero on it, the FY and Month Filters are null and void and it shows me as far back as my date table goes. I have a dates table and a previous dates table in my BI. The visuals work correctly when I use the regular MyResult calculation, but when I attempt to use the MyResult w 0 it doesn't.
MyResult = Divide([Total Results],[Total Division])
MyResult w 0 = [MyResult]+0
Filtered Month & Year = SELECTEDVALUE(_Dates[Month & Year])
MyResult L13M =
The result is returned with dates going back to July 2022 which is how far my date table goes back. If I switch to without the zero, it goes back properly to the Month & Year I have selected in the filters. Is there something somewhere I can change in one of my codes to correct this?
2 Replies
Hi rachels920 - Ensure the relationship between Date and Previous Date is correct and active only when needed viaUSERRELATIONSHIP. Filtered Month & Year: Verify that correctly reflects the month and year you're filtering on. If this is not working as expected, you might want to inspect how this measure is defined.
MyResult L13M =
VAR SelectedMonth = [Filtered Month & Year]
VAR PreviousDates =
DATESINPERIOD(
'Previous Date'[Date],
SelectedMonth,
-13,
MONTH
)
VAR Result =
CALCULATE(
[MyResult], // Use the original MyResult calculation
REMOVEFILTERS(_Dates), // Remove filters from the main date table
KEEPFILTERS(PreviousDates), // Apply 13-month date filter
USERELATIONSHIP(_Dates[Date], 'Previous Date'[Date])
)-- Include zero for missing values but respect the date filter
RETURN IF(NOT(ISBLANK(Result)), Result, 0)Hope it helps.
Its still not working 😞
I set the relationship up a while ago, but had it set as one to many but not active.
