Forum Discussion

ScoRusTAFENSW's avatar
ScoRusTAFENSW
Frequent Visitor
6 years ago
Solved

Count todays date based on date range in two columns

Hi all,    I am trying to create a Card that shows how many people we have off today.   Data is fairly simple;         Start date of leave and end date of leave (fyi, return to work wo...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi ScoRusTAFENSW ,

    Use below measure to count of leave based on selection date,

    Leave Count =
    VAR Selected_Date = IF(HASONEVALUE(Leave[StartDate]),FIRSTNONBLANK(Leave[StartDate],TODAY()),TODAY())
    VAR Leave_ = CALCULATE(COUNTROWS(Leave),
    FILTER(Leave,
    AND(Leave[StartDate]<=Selected_Date,Leave[EndDate]>=Selected_Date)
    )
    )
    RETURN
    IF(ISBLANK(Leave_),0,Leave_)


    Output:

     
    Best Regards,
    Mail2inba4

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  • ScoRusTAFENSW's avatar
    ScoRusTAFENSW
    6 years ago

    Thank you Anonymous,  

    It works perfectly!


    If you have the time, could you please explain how it works?

    I think I understand the filter less than and greater than section.

    But a little unsure about the Selected_Date VAR details.

     

    Thanks again.

     

     

     

     

     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi ScoRusTAFENSW ,

Use below measure to count of leave based on selection date,

Leave Count =
VAR Selected_Date = IF(HASONEVALUE(Leave[StartDate]),FIRSTNONBLANK(Leave[StartDate],TODAY()),TODAY())
VAR Leave_ = CALCULATE(COUNTROWS(Leave),
FILTER(Leave,
AND(Leave[StartDate]<=Selected_Date,Leave[EndDate]>=Selected_Date)
)
)
RETURN
IF(ISBLANK(Leave_),0,Leave_)


Output:

 
Best Regards,
Mail2inba4

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ScoRusTAFENSW's avatar
ScoRusTAFENSW
Frequent Visitor
6 years ago

Thank you Anonymous,  

It works perfectly!


If you have the time, could you please explain how it works?

I think I understand the filter less than and greater than section.

But a little unsure about the Selected_Date VAR details.

 

Thanks again.

 

 

 

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi ScoRusTAFENSW ,

    Selected_Date returns a date based on your filter selection, If you select only one day in filter it will return selected date or else return today. Based on selected_Date it will calculate the absent count.

    Best Regards,
    Mail2inba4