Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Counting with a date slicer

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)!

Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi Anonymous,

     

    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

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi Anonymous,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        MFelix
        Super User
        8 years ago

        Hi Anonymous,

         

        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

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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)!