Forum Discussion

Hemanth96's avatar
Hemanth96
Frequent Visitor
3 years ago

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 

Need Help

5 Replies

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    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.

    • Hemanth96's avatar
      Hemanth96
      Frequent Visitor
      3 years ago

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

      • v-yadongf-msft's avatar
        v-yadongf-msft
        Community Support
        3 years ago

        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.

  • Hemanth96's avatar
    Hemanth96
    Frequent Visitor
    3 years ago

    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.