Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BWL
Helper II
Helper II

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!



 









1 ACCEPTED SOLUTION
BWL
Helper II
Helper II

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

image.png

 

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.

View solution in original post

4 REPLIES 4
BWL
Helper II
Helper II

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

image.png

 

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
Community Support
Community Support

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.

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
Community Support
Community Support

Hi BWL,

 

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

 

Best Regards,

Zoe Zhi

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.