Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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]))
A | 12/09/2022 |
B | 12/09/2022 |
C | 12/11/2022 |
D | 12/12/2022 |
E | 12/13/2022 |
F | 12/14/2022 |
G | 12/10/2022 |
H | 11/18/2022 |
I | 12/03/2022 |
J | 12/07/2022 |
K | 12/12/2022 |
L | 12/14/2022 |
M | 12/14/2022 |
N | 12/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?
@v-yadongf-msft
Here's my sample data.
Name Submitted date
A | 12/09/2022 |
B | 12/09/2022 |
C | 12/11/2022 |
D | 12/12/2022 |
E | 12/13/2022 |
F | 12/14/2022 |
G | 12/10/2022 |
H | 11/18/2022 |
I | 12/03/2022 |
J | 12/07/2022 |
K | 12/12/2022 |
L | 12/14/2022 |
M | 12/14/2022 |
N | 12/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.
Hi @Hemanth96 ,
This is my test table:
Calendar table:
Create a measure:
Measure = CALCULATE(COUNT('Table'[Submitted date]),DATESBETWEEN('Table'[Submitted date],MIN('CalendarTable'[Date]),MAX('CalendarTable'[Date])))
Result:
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)))
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
96 | |
87 | |
70 |
User | Count |
---|---|
166 | |
133 | |
129 | |
102 | |
98 |