Counting with a date slicer

[GuillemXII]

Hello everyone,

I'm building a dashboard that is supposed to give information about absentism and sick leaves in a company, but a problem arose:

I would like to be able to calculate how many people had active sick leaves during a concreate day (indicated by a slicer), but the way in wich the sick leaves are registred is the following:

Worker            Tipology                Start Date                   End Date

35                    Sick leave              12/03/2018                02/04/2018
24                    Sick leave              29/01/2018                07/05/2018

Beacuse there are not multiple registres for each day of the sick leave, but a single one containing the start and the end of it, I would need to create a measure that basically worked as following:

"CALCULATE (COUNTROWS (Absence); Absence[Tipology]="Sick leave"; Absence[Start Date]<=slicer date && Absence[End Date]>=slicer date)"

But I already know that this is not possible. Therefore, wich do you think would be the easiest way of obtaining how many people had active sick leaves during a concreate day (indicated by a slicer)?

Thankyou in advance and sorry for any language error (not a native speaker)!

[GuillemXII]

Hello everyone, 

First of all, sorry for the awful title, but I didn't found a better one.

I'm building a dashboard that is supposed to give information about absentism and sick leaves in a company, but a problem arose:

I would like to be able to calculate how many people had active sick leaves during a concreate day (indicated by a slicer), but the way in wich the sick leaves are registred is the following:

Worker             Tipology                 Start Date                 End Date

35                     Sick leave               12/03/2018               02/04/2018

24                     Sick leave               29/01/2018               07/05/2018

Beacuse there are not multiple registres for each day of the sick leave, but a single one containing the start and the end of it, I would need to create a measure that basically worked as following:

"CALCULATE (COUNTROWS (Absence); Absence[Tipology]="Sick leave"; Absence[Start Date]<=slicer date && Absence[End Date]>=slicer date)"

But I already know that this is not possible. Therefore, which do you think would be the easiest way of obtaining how many people had active sick leaves during a concreate day (indicated by a slicer)?

Thankyou in advance and sorry for any language mistake (I'm not a native speaker)!

[MFelix · Accepted solution]

Hi @GuillemXII,

Create a Dimesion date table and then add the following measure:

Sick_Leave_Count =
VAR start_date =
    MIN ( DimDate[Date] )
RETURN
    CALCULATE (
        COUNT ( Absent[Worker] );
        FILTER (
            ALL ( Absent[Start Date]; Absent[End Date] );
            Absent[Start Date] <= start_date
                && Absent[End Date] >= start_date
        )
    )
        + 0

Regards,

MFelix

[GuillemXII]

Thank you very much, it worked perfectly!

However, could you explain me how the "VAR"+"RETURN" work? I've never used those comands and I don't understand what they are doing here...

VAR start_date =
    MIN ( DimDate[Date] )
RETURN

[MFelix]

Hi @GuillemXII,

When you create calculated measures or columns you can setup difference variables (VAR) in order to simplify your measure, so when youn have a recurring calculation that you need to use several times in your measure you can create a variable or several, and use it instead of having to write the same code over and over again.

The Return part is the part where you define the start of your result calculation.

Check this link where you have an explanation of the use of variables in DAX.

Regards,

MFelix