Calculation between Start and End Date

[Anonymous]

Hi

I have one table named "AbsenceDetailsFact" with columns named date_start, date_end, absence_days. following snap is the data from the table (sample data for one employee). I want to map fact table "AbsenceDetailsFact" with Date dimension table and want to calculate the number of absent employees for a month, year, quarter (user select from slicer). If you look at the row heighlighted, row is showing 4 absence_days because 2 holidays come in between that date range. How to handle this requirement ?

[Anonymous]

HI @Anonymous ,

You can use following calculated column formula to manually calculate workdays:

Workday =
COUNTROWS ( FILTER ( CALENDAR ( [Start], [End] ), WEEKDAY ( [Date], 2 ) < 6 ) )

Regards,
Xiaoxin Sheng

[Anonymous]

Can we create measure instead of column that can accept Start and End date ?

[Anonymous · Accepted solution]

Hi @Anonymous ,

Yes, it is possible. I modify my formula to add two variable to get current start,end date for calculation.

Workday =
VAR currStart =
    MIN ( Table[Start] )
VAR currEnd =
    MAX ( Table[End] )
RETURN
    COUNTROWS (
        FILTER ( CALENDAR ( currStart, currEnd ), WEEKDAY ( [Date], 2 ) < 6 )
    )

Regards,

Xiapxin Sheng

[Greg_Deckler]

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365