Calculate Monthly Lost Working Days

[Cobra77]

Hi,

I want calculate Monthly Lost Working Days.

Sample Data ;

it's just a small data set, in reality , we have millions of lines in fact table '90 - Accidents'

DateAccident is connected with '01 - Calendar'

'Calendar' is connected with nothing

We have a slicer on '01 - Calendar'[Dates] to filter the pbix report.

Measure :

Nb lostdays :=
VAR AllPossibleDates = GENERATE('90 - Accidents';'Calendar')
VAR FlagDates = FILTER(ADDCOLUMNS(AllPossibleDates;"Flag";IF('Calendar'[Date]>IF(ISBLANK('90 - Accidents'[lostTimeStartDate]);NOW();'90 - Accidents'[lostTimeStartDate] ) && 'Calendar'[Date]<=IF(ISBLANK('90 - Accidents'[lostTimeEndDate]);NOW();'90 - Accidents'[lostTimeEndDate]);1;0));[Flag]=1)
RETURN
SUMX(FlagDates;[Flag])

and the graphic :

but the result its false. ( Axe : Calendar table )

How filter only on july (in this example) like the slicer , or limit on the year ( year max of slicer) ? ,  and how to remove the date filter in the calculation, if I have accidents before July and they end during or after.

Thanks for your helps

[v-cherch-msft]

Hi @Cobra77

You may try to use below measure. Here is the sample file for your reference. Please let me know if that makes sense.

Nb lostdays =
VAR a =
    FILTER (
        GENERATE ( 'Calendar', '90 - Accidents' ),
        '90 - Accidents'[lostTimeStartDate] <= 'Calendar'[Date]
            && '90 - Accidents'[lostTimeEndDate] >= 'Calendar'[Date]
    )
RETURN
    COUNTROWS ( a ) + 0

Regards,

Cherie

[Cobra77]

hi @v-cherch-msft

thanks for your test and file,

but the good result for july its 67 lots days , not only 5

miss people1 and 2 , not present in july

and your calendar its only 2018 , we have 2010 to 2018.

we must limit the calendar dynamically follow the slicer.

Thanks if you find.