Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
markyochoa
Helper I
Helper I

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. 

markyochoa_0-1676474925259.png

 

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. 

 

CALCULATE(COUNT(Calendar[Date]), FILTER( Calendar, Calendar[Date] >= [BeginningMonth] && Calendar[Date] <= [SelectedDate]))
 
COUNT(Calendar[Date]) and COUNTROWS(Calendar) both have the same result.
 
These are the values that are within each Measure. 

markyochoa_1-1676474958406.png

 

SelectedDate = MAX(Calendar[Date])

 

BeginningMonth = DATE(YEAR([SelectedDate]),MONTH([SelectedDate]),1)

 

I don't know what else to do from here.

1 ACCEPTED SOLUTION

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@markyochoa Try:

COUNTROWS(
  FILTER( 
    Calendar, 
    Calendar[Date] >= [BeginningMonth] && Calendar[Date] <= [SelectedDate]
  )
)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks!!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.