Count of workdays missing worktime report by employee

[henkka]

Hi,

Can someone help, I need to calculate the count of days by employee where no worktime booking made in current month but can't get it right

Calendar table Startdatesdim[Start date]

Startdatesdim[Day of week] (day numbers from 1 to 7)

Employees[Employee.id]

Worktime[Employee.id]

Worktime[Start date]

[Idrissshatila · Accepted solution]

Hello @henkka ,

check of you could use this video for your case https://youtu.be/6JA6uy3ZRBA

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍

Follow me on Linkedin

[henkka]

Hi and thank you for your response. Your video led me to a solution with this. Altough its not straight forward I'll mark it as a solution. Here is the DAX that I came up with, maybe there is more efficient or reasonable ways to do this same but anyways

Days missing report - this month =

VAR _IsWorkday = FILTER(StartDatesDim , StartDatesDim[Is workday] = 1 )

VAR _Daterange = FILTER(StartDatesDim , StartDatesDim[Start date] >= EOMONTH(TODAY(), -1 ) +1 || StartDatesDim[Start date] <= TODAY())

VAR _Recordexists =

IF (

    countrows (

        FILTER ( FactWorktime  ,

                FactWorktime[Employee.id] = SELECTEDVALUE ( FactWorktime[Employee.id] )

                &&  FactWorktime[Start date] >= EOMONTH(TODAY() , -1 ) +1

                || FactWorktime[Start date] <= TODAY()

        )

    )

    >0 , DISTINCTCOUNT ( FactWorktime[Start date]), 0

)

VAR _Totalworkdays =

CALCULATE (

    COUNTROWS ( StartDatesDim )  ,

    _Daterange ,

    _IsWorkday

    )

VAR _Employeeworkdaybookings =

SUMX (

    DISTINCT ( FactWorktime[Employee.id] ) ,

    CALCULATE (

        COUNTROWS ( FactWorktime ) ,

        MONTH ( FactWorktime[Start date] ) = MONTH ( TODAY() ) ,

        FactWorktime[Employee.id] = EARLIER ( FactWorktime[Employee.id] )

    )

)

VAR _MissingDays = _Employeeworkdaybookings - _Totalworkdays

VAR Result = IF ( _MissingDays = -_Totalworkdays  ,  0  ,  _MissingDays * -1 )

RETURN

Result