Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power BI DATESBETWEEN

I am using DATESBETWEEN Dax formula to create a column(see below) .  I have a Date Table. The formula is returning blanks for all rows except where the Fully approved date is blank.  Any thoughts on what I may be doing incorrectly?  

 

Wkdays w/o Holidays col =
CALCULATE(
COUNTROWS(Dates),
DATESBETWEEN(Dates[Date],
'Open pending Requisitions'[Today's date],
'Open pending Requisitions'[First Fully Approved Date]),
FILTER(
Dates,
Dates[IsWorkDay] = TRUE),
ALLSELECTED(Dates))
 
BUD1IdentifierToday's dateApproved DateWkdays w/o Holidays col
GLOBAL 16/21/2022 0:003/1/2022 0:00 
Operations26/21/2022 0:002/22/2022 0:00 
Global36/21/2022 0:003/7/2022 0:00 
Regional46/21/2022 0:003/3/2022 0:00 
Operations56/21/2022 0:003/3/2022 0:00 
Static66/21/2022 0:002/18/2022 0:00 
GLOBAL 76/21/2022 0:00 139
Regional86/21/2022 0:005/3/2022 0:00 
Static96/21/2022 0:00 139
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    In the DATESBETWEEN function -"DATESBETWEEN(<Dates>, <StartDate>, <EndDate>)", the second parameter is start date and the third parameter is end date. If the start date is biger than the end date, it will return blank.

    You can modify the formula like this:

    Wkdays w/o Holidays col =
IF (
    ISBLANK ( MAX ( 'Open pending Requisitions'[Approved Date] ) ),
    CALCULATE (
        COUNTROWS ( 'Dates' ),
        DATESBETWEEN (
            'Dates'[Date],
            MAX ( 'Open pending Requisitions'[Today's date] ),
            MAX ( 'Open pending Requisitions'[Approved Date] )
        ),
        FILTER ( Dates, Dates[IsWorkDay] = TRUE ),
        ALLSELECTED ( 'Dates'[Date] )
    ),
    CALCULATE (
        COUNTROWS ( 'Dates' ),
        DATESBETWEEN (
            'Dates'[Date],
            MAX ( 'Open pending Requisitions'[Approved Date] ),
            MAX ( 'Open pending Requisitions'[Today's date] )
        ),
        FILTER ( Dates, Dates[IsWorkDay] = TRUE ),
        ALLSELECTED ( 'Dates'[Date] )
    )
)

    Here, the rows without Approved Date will retain the same result, other rows will get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

1 Reply

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    In the DATESBETWEEN function -"DATESBETWEEN(<Dates>, <StartDate>, <EndDate>)", the second parameter is start date and the third parameter is end date. If the start date is biger than the end date, it will return blank.

    You can modify the formula like this:

    Wkdays w/o Holidays col =
IF (
    ISBLANK ( MAX ( 'Open pending Requisitions'[Approved Date] ) ),
    CALCULATE (
        COUNTROWS ( 'Dates' ),
        DATESBETWEEN (
            'Dates'[Date],
            MAX ( 'Open pending Requisitions'[Today's date] ),
            MAX ( 'Open pending Requisitions'[Approved Date] )
        ),
        FILTER ( Dates, Dates[IsWorkDay] = TRUE ),
        ALLSELECTED ( 'Dates'[Date] )
    ),
    CALCULATE (
        COUNTROWS ( 'Dates' ),
        DATESBETWEEN (
            'Dates'[Date],
            MAX ( 'Open pending Requisitions'[Approved Date] ),
            MAX ( 'Open pending Requisitions'[Today's date] )
        ),
        FILTER ( Dates, Dates[IsWorkDay] = TRUE ),
        ALLSELECTED ( 'Dates'[Date] )
    )
)

    Here, the rows without Approved Date will retain the same result, other rows will get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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