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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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