Rolling dates

[BWL]

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!

[BWL · Accepted solution]

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.

[dax]

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]

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]

Hi BWL,

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

Best Regards,

Zoe Zhi