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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Hemanth96
Frequent Visitor

Datesbetween function not working with harvested slicers

Hi, I'm new to Power BI and trying to build a measure to calculate no. of records which have dates between week start date and week end date. 

I have created a slicer with calendar dates and harvesting the dates into two measure with following DAX

 

DAX

- Week start date = CALCULATE(Min('Calendar'[Date]),ALLSELECTED('Calendar'))

- Week end date =  CALCULATE(Max('Calendar'[Date]),ALLSELECTED('Calendar'))

 

 

Now I created another measure to count no. of records having Submitted Dates between week start and end dates

 

DAX

- #Submissions per week = CALCULATE(COUNT('Tracker'[Submitted Date]), DATESBETWEEN('Tracker'[Submitted Date].[Date],[Week start date],[Week end date]))

 
But its counting all the records which is not the case. 
 
Sample data
Name Submitted date
A12/09/2022
B12/09/2022
C12/11/2022
D12/12/2022
E12/13/2022
F12/14/2022
G12/10/2022
H11/18/2022
I12/03/2022
J12/07/2022
K12/12/2022
L12/14/2022
M12/14/2022
N12/01/2022


Total Submissions between 12/04/2022 and 12/10/2022 should be 4 from the above sample data.

But its counting all the records (14) which is not the case. Where did I go wrong?

 

@amitchandak @Greg_Deckler @Mikelytics @MFelix @Jihwan_Kim 

5 REPLIES 5
Hemanth96
Frequent Visitor

@v-yadongf-msft 
Here's my sample data. 

Name Submitted date

A12/09/2022
B12/09/2022
C12/11/2022
D12/12/2022
E12/13/2022
F12/14/2022
G12/10/2022
H11/18/2022
I12/03/2022
J12/07/2022
K12/12/2022
L12/14/2022
M12/14/2022
N12/01/2022



Total Submissions between 12/04/2022 and 12/10/2022 should be 4 from the above sample data.

But its counting all the records (14) which is not the case.
I'm harvesting the slicer date ranges from above DAX (week start and week end dates) since the user should have the flexibility to choose the date ranges.

v-yadongf-msft
Community Support
Community Support

Hi @Hemanth96 ,

 

This is my test table:

vyadongfmsft_0-1670900296406.png

 

Calendar table:

vyadongfmsft_1-1670900333611.png

 

Create a measure:

Measure = CALCULATE(COUNT('Table'[Submitted date]),DATESBETWEEN('Table'[Submitted date],MIN('CalendarTable'[Date]),MAX('CalendarTable'[Date])))

 

Result:

vyadongfmsft_2-1670900408684.png

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Yadong, but how do I get the count of dates only between 1/1/2022 and 6/1/2022 ? 

Hi @Hemanth96 ,

 

Measure = CALCULATE(COUNT('Table'[Submitted date]),DATESBETWEEN('Table'[Submitted date],MIN('CalendarTable'[Date]),DATE(2022,6,1)))

vyadongfmsft_0-1670913828874.png

 

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

The main goal is - I want the user to have flexibility to select the date ranges (start and end date); and my calendar dates start from 2019, so both start and end dates have to be flexible for the user.  

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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