Count todays date based on date range in two columns

[ScoRusTAFENSW]

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 would be next working day after end date).

The data is not linked to anything else in the Dashboard. 

I had it set up and almost working with this;

Leave Count =

Var LeaveCount = COUNTA('Leave Tracker Log'[Start Date])

Return

IF(ISBLANK(LeaveCount), "0", LeaveCount)

With a relative date filter for in the last 1 day. 

But realised it was missing anyone that had more than one day off as it was only looking at the start date.

For example;

I have one day off, start date and end date would both be 04/12/2019.

- Measure would be correct and count me off today based on start date (04/12/2019).

I have three days off, start date would be 04/12/2019 and end date 06/12/2019.

- The current measure would only count me off today (04/12/2019) but not the next two days as it is not looking at the end date (or the range between start and end date). 

My first instinct was to go back to Excel and list all dates between the Start Date and End Date columns and count how many times todays date shows up.
But also feel that this would be too messy for what should be a simple Card and would like to keep the Excel data as un-manipulated as possible. 

I am at a bit of a loss on what to do. 

Thanks in advance. 

EDIT:

@Anonymous 
Output is to a Card;

Not sure about the sample data, sorry.

As posted above its just 2 columns with dates (Start Date and End Date).

Do I need to attach an excel doc with some of the raw data?

[Anonymous · Accepted solution]

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.

[Anonymous · Accepted solution]

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 · Accepted solution]

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]

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

[Anonymous]

Hi @ScoRusTAFENSW ,

Please share the sample data and output.

Best Regards,
Mail2inba4