Forum Discussion

Farmertree's avatar
Farmertree
Frequent Visitor
7 years ago
Solved

Days between two dates and a date slicer

I would like to count a total number of available days between a variable range of two dates defined by a slicer. I have a table with employees ID, start date and end date. Missing end dates indicate...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Farmertree ,

    Yes, it is possible. You can add variables to extract min and max date from selected calendar date and compare with start/end date to get datediff. After these, package them with a sumx function to summary datediff result.

    Meausre =
VAR selected =
    ALLSELECTED ( Calendar[Date] )
VAR _max =
    MAXX ( selected, [Date] )
VAR _min =
    MINX ( selected, [Date] )
RETURN
    SUMX (
        ADDCOLUMNS (
            ALLSELECTED ( Table ),
            "Diff", DATEDIFF ( MAX ( [Start date], _min ), MIN ( [End date], _max ), DAY )
        ),
        [Diff]
    )

    Regards,

    Xiaoxin Sheng

  • Farmertree's avatar
    Farmertree
    7 years ago

    Anonymous Thanks! This helps a lot. The only problem is that this formula returns a negative DATEDIFF value for End dates < _min. Do you know how i could apply a filter within this formula that only selects rows in which Enddate >= _min?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Farmertree ,

Yes, it is possible. You can add variables to extract min and max date from selected calendar date and compare with start/end date to get datediff. After these, package them with a sumx function to summary datediff result.

Meausre =
VAR selected =
    ALLSELECTED ( Calendar[Date] )
VAR _max =
    MAXX ( selected, [Date] )
VAR _min =
    MINX ( selected, [Date] )
RETURN
    SUMX (
        ADDCOLUMNS (
            ALLSELECTED ( Table ),
            "Diff", DATEDIFF ( MAX ( [Start date], _min ), MIN ( [End date], _max ), DAY )
        ),
        [Diff]
    )

Regards,

Xiaoxin Sheng

Farmertree's avatar
Farmertree
Frequent Visitor
7 years ago

Anonymous Thanks! This helps a lot. The only problem is that this formula returns a negative DATEDIFF value for End dates < _min. Do you know how i could apply a filter within this formula that only selects rows in which Enddate >= _min?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI Farmertree ,

    You can do compare between date and _min to get the bigger one and use in datediff function:

    Measure =
VAR selected =
    ALLSELECTED ( Calendar[Date] )
VAR _max =
    MAXX ( selected, [Date] )
VAR _min =
    MINX ( selected, [Date] )
RETURN
    SUMX (
        ADDCOLUMNS (
            ALLSELECTED ( Table ),
            "Diff", DATEDIFF (
                MAX ( [Start date], _min ),
                MIN ( MAX ( [End date], _min ), _max ),
                DAY
            )
        ),
        [Diff]
    )

    Regards,

    Xiaoxin Sheng