Forum Discussion
Custom Days Between Measure not working
I have an independent calendar that I use for most calculations involving dates. It's a simple 1-column table with dates from 1/1/2021 - 12/31/2023. I'm sure almost everyone is familiar with this practice.
This is the filter as it appears on the dashboard. The beginning date is hidden behind an empty textbox so the user only just sees the ending date.
This is the formula that I used, and it makes sense to me. But what I found is the "Calendar[Date] <= [SelectedDate]" isn't being evaluated and I'm getting a COUNT() of everything from 1/1/2021 - 1/16/2023.
SelectedDate = MAX(Calendar[Date])
BeginningMonth = DATE(YEAR([SelectedDate]),MONTH([SelectedDate]),1)
I don't know what else to do from here.
markyochoa Looks like it has to do with the evaluation of the measures within the FILTER clause, do this instead:
Days = VAR __Begin = [BeginningMonth] VAR __Selected = [SelectedDate] VAR __Table = FILTER( 'Dates', [Date] >= __Begin && [Date] <= __Selected) VAR __Result = COUNTROWS(__Table) RETURN __Result
4 Replies
markyochoa Try:
COUNTROWS( FILTER( Calendar, Calendar[Date] >= [BeginningMonth] && Calendar[Date] <= [SelectedDate] ) )Hey Greg_Deckler ,
Thanks for the quick response. Unfortunately I get the same result.
I was thinking maybe it was something to do with my dashboard setup, but I created a standalone Pbix with just the Calendar table and the 3 measures and it remains the same.
I will say this though, if I change the 1/1/2021 to 1/1/2023 it will work, but that defeats the purpose of why I use the Calendar table in the first place.
markyochoa Looks like it has to do with the evaluation of the measures within the FILTER clause, do this instead:
Days = VAR __Begin = [BeginningMonth] VAR __Selected = [SelectedDate] VAR __Table = FILTER( 'Dates', [Date] >= __Begin && [Date] <= __Selected) VAR __Result = COUNTROWS(__Table) RETURN __Result
