Forum Discussion

BWL's avatar
BWL
Helper II
7 years ago
Solved

Rolling dates

Dear all,

I've searched in the community, got some good info and I'm close but still can't get it fixed. 
I have the following tables:

- Employees  table which contains  the columns: "EmployeeID", "Start Date" and "End Date"
- Absence table which containes the columsn: "Employee ID", "Start Date" and "End Date"

My ultimate goal is to calculate the absence hours and divide them by the number of employees in a specific weeknumber and show it on a linechart.

The number of employees I use: 

NumberOfEmployees= 

CALCULATE (
    DISTINCTCOUNT(Employees[ID]);
    FILTER(
        Employees;
        Employees[Start date]
            <= LASTDATE ( 'Date'[Date])
            && Employees[End date]
                >= FIRSTDATE ( 'Date'[Date]) || ISBLANK(Employees [End date])
    )
)

To get this worked I created a datetable without a relationship.
In this datetable is the weeknumber defined aswell, which I use in the linechart. 

Unfortunatly I cant get it done to calculate the absence hours for a given time.

Hope anyone can help me.

Many thanks!



 









Need Help
  • BWL's avatar
    BWL
    7 years ago

    Hi dax ,


    After some real DAX struggles I found a solution, it might be not the perfect DAX, but for now it works.
    I had three tables:

    1. Employee table 1 - N 2, Absence table
    3. Date table

     

    Absence Hours = 
VAR OutsideWeek =       //time between start week ---> end week (+1 to exclude sunday)
    SUMX (
        FILTER (
            Absence;
            Absence[StartDate]
                <= FIRSTDATE ( 'Date'[Date] )
                && Absence[EndDate]
                    >= LASTDATE ( 'Date'[Date]))
        ;
        
            DATEDIFF (
                (FIRSTDATE( 'Date'[Date] ) + 1);
                LASTDATE(  'Date'[Date] );
                DAY
            ) * 8
         
    )
VAR StartIsOutSideWeek =  // time between start absence ---> end week
    SUMX (
        FILTER (
            Absence;
            Absence[StartDate]
                > FIRSTDATE ( 'Date'[Date] )
                && Absence[StartDate]
                    < LASTDATE ( 'Date'[Date] )
                && Absence[EndDate]
                    >= LASTDATE ( 'Date'[Date] )) ;
                 
        DATEDIFF (
            Absence[StartDate];
            LASTDATE ( 'Date'[Date] );
            DAY
        ) * 8
    )

VAR EndOutsideWeek = // time between start week  ---> end absence (+1 day to exclude sunday)
    SUMX (
        FILTER (
            Absence;
            Absence[StartDate]
                < FIRSTDATE ( 'Date'[Date] )
                && Absence[EndDate]
                > FIRSTDATE( 'Date'[Date] )
                && Absence[EndDate]
                < LASTDATE ( 'Date'[Date] ) 

        );
        DATEDIFF (
            (FIRSTDATE('Date'[Date])+1);
            Absence[EndDate];
            DAY
        ) * 8
    )


VAR InsideWeek =  // time between start absence ---> end absence
    SUMX (
        FILTER (
            Absence;
            Absence[StartDate]
                >= FIRSTDATE ( 'Date'[Date] )
                && Absence[EndDate]
                <= LASTDATE ( 'Date'[Date] )

        );
        DATEDIFF (
            Absence[StartDate];
            Absence[EndDate];
            DAY
        ) * 8
    ) 


RETURN
   OutsideWeek + StartIsOutSideWeek + EndOutsideWeek + InsideWeek +[Open Absence Hours]
    Open Absence Hours = // calculates the hours for absence with end day blank()
VAR StartPreviousWeek =  // time between start absence in previous week ---> end week
    SUMX (
        FILTER (
            Absence;
            Absence[StartDate]
                < LASTDATE ( 'Date'[Date] )
                && Absence[StartDate]
                    > FIRSTDATE ( 'Date'[Date] )
                && Absence[EndDate]
                    = BLANK ()
        );

        DATEDIFF (
            Absence[StartDate];
            LASTDATE ( 'Date'[Date] );
            DAY
        ) * 8
    )
    


VAR StartOutsideWeek =
    SUMX (
        FILTER (
            Absence;
            Absence[StartDate]
                <= FIRSTDATE ( 'Date'[Date] )
                && Absence[EndDate]
                    = BLANK ()
        );

DATEDIFF (
            (
                FIRSTDATE ( 'Date'[Date] ) + 1
            );
            LASTDATE ( 'Date'[Date] );
            DAY
        ) * 8     
        )
RETURN
    StartOutsideWeek + StartPreviousWeek

     

    For more details, you can check the file here:

    https://www.dropbox.com/s/jte4onfidloiepz/Example.pbix?dl=0

    Thanks,


    Bwl.

4 Replies

  • dax's avatar
    dax
    Community Support
    7 years ago

    Hi BWL,

    I can’t reproduce your design just based on your description, so if possible , could you please inform  me more detailed information (such as your sample data and your expecting output)? Then I will help you more correctly.

    You could refer to How to Get Your Question Answered Quickly for details.

    Best Regards,

    Zoe Zhi

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • BWL's avatar
      BWL
      Helper II
      7 years ago

      Hi dax,


      Just created at example pbix file. 
      Can I share it directly with you, because I don't see where I can upload it in this forum. 

      KInd regards.

       

       

       

       

      • dax's avatar
        dax
        Community Support
        7 years ago

        Hi BWL,

         

        You could try to upload pbix file in onedrive or other place which we could access to .

         

        Best Regards,

        Zoe Zhi

  • BWL's avatar
    BWL
    Helper II
    7 years ago

    Hi dax ,


    After some real DAX struggles I found a solution, it might be not the perfect DAX, but for now it works.
    I had three tables:

    1. Employee table 1 - N 2, Absence table
    3. Date table

     

    Absence Hours = 
VAR OutsideWeek =       //time between start week ---> end week (+1 to exclude sunday)
    SUMX (
        FILTER (
            Absence;
            Absence[StartDate]
                <= FIRSTDATE ( 'Date'[Date] )
                && Absence[EndDate]
                    >= LASTDATE ( 'Date'[Date]))
        ;
        
            DATEDIFF (
                (FIRSTDATE( 'Date'[Date] ) + 1);
                LASTDATE(  'Date'[Date] );
                DAY
            ) * 8
         
    )
VAR StartIsOutSideWeek =  // time between start absence ---> end week
    SUMX (
        FILTER (
            Absence;
            Absence[StartDate]
                > FIRSTDATE ( 'Date'[Date] )
                && Absence[StartDate]
                    < LASTDATE ( 'Date'[Date] )
                && Absence[EndDate]
                    >= LASTDATE ( 'Date'[Date] )) ;
                 
        DATEDIFF (
            Absence[StartDate];
            LASTDATE ( 'Date'[Date] );
            DAY
        ) * 8
    )

VAR EndOutsideWeek = // time between start week  ---> end absence (+1 day to exclude sunday)
    SUMX (
        FILTER (
            Absence;
            Absence[StartDate]
                < FIRSTDATE ( 'Date'[Date] )
                && Absence[EndDate]
                > FIRSTDATE( 'Date'[Date] )
                && Absence[EndDate]
                < LASTDATE ( 'Date'[Date] ) 

        );
        DATEDIFF (
            (FIRSTDATE('Date'[Date])+1);
            Absence[EndDate];
            DAY
        ) * 8
    )


VAR InsideWeek =  // time between start absence ---> end absence
    SUMX (
        FILTER (
            Absence;
            Absence[StartDate]
                >= FIRSTDATE ( 'Date'[Date] )
                && Absence[EndDate]
                <= LASTDATE ( 'Date'[Date] )

        );
        DATEDIFF (
            Absence[StartDate];
            Absence[EndDate];
            DAY
        ) * 8
    ) 


RETURN
   OutsideWeek + StartIsOutSideWeek + EndOutsideWeek + InsideWeek +[Open Absence Hours]
    Open Absence Hours = // calculates the hours for absence with end day blank()
VAR StartPreviousWeek =  // time between start absence in previous week ---> end week
    SUMX (
        FILTER (
            Absence;
            Absence[StartDate]
                < LASTDATE ( 'Date'[Date] )
                && Absence[StartDate]
                    > FIRSTDATE ( 'Date'[Date] )
                && Absence[EndDate]
                    = BLANK ()
        );

        DATEDIFF (
            Absence[StartDate];
            LASTDATE ( 'Date'[Date] );
            DAY
        ) * 8
    )
    


VAR StartOutsideWeek =
    SUMX (
        FILTER (
            Absence;
            Absence[StartDate]
                <= FIRSTDATE ( 'Date'[Date] )
                && Absence[EndDate]
                    = BLANK ()
        );

DATEDIFF (
            (
                FIRSTDATE ( 'Date'[Date] ) + 1
            );
            LASTDATE ( 'Date'[Date] );
            DAY
        ) * 8     
        )
RETURN
    StartOutsideWeek + StartPreviousWeek

     

    For more details, you can check the file here:

    https://www.dropbox.com/s/jte4onfidloiepz/Example.pbix?dl=0

    Thanks,


    Bwl.