Measure automatic days worked and total work days in month

[Nick2358]

Hello everyone,

I'm trying to figure out a measure that would allow me to automate the calculation of my "Run Rate" and not have to manually change it every morning.

The dataset presents itself as below:

Name  Actual  Target   Run Rate

XXX        4          7           6%

As of November 4th the calculation is ((Actual/Days worked)*Total Days worked in Month/Target)

                                                              ((4/2)*21/7) = 6

What I'd need is to have the "Days worked" (2) automating each day of the week (as we don't take the weekends into account) and also the total days worked (most of the time is 21 but when there is a holiday it needs to be removed from the calculation)

I guess it is a long shot but is there any way to automate this?

Thanks in advance for the help,

Nick

[DataInsights]

@Nick2358,

Try this measure. You will need a Date table with column [Is Working Day]. Let me know if you need assistance creating the Date table.

Run Rate = 
VAR vName =
    MAX ( RunRateTable[Name] )
VAR vActual =
    MAX ( RunRateTable[Actual] )
VAR vTarget =
    MAX ( RunRateTable[Target] )
VAR vToday =
    TODAY ()
VAR vYear =
    YEAR ( vToday )
VAR vMonth =
    MONTH ( vToday )
VAR vDaysWorked =
    CALCULATE (
        COUNT ( Dates[Date] ),
        Dates[Is Working Day] = 1,
        Dates[Year] = vYear,
        Dates[Month Number] = vMonth,
        Dates[Date] < vToday
    )
VAR vTotalDaysWorked =
    CALCULATE (
        COUNT ( Dates[Date] ),
        Dates[Is Working Day] = 1,
        Dates[Year] = vYear,
        Dates[Month Number] = vMonth
    )
VAR vResult =
    DIVIDE ( vActual, vDaysWorked ) * DIVIDE ( vTotalDaysWorked, vTarget )
RETURN
    vResult

[Nick2358]

Hi @DataInsights ,

I already had a date table so I created a [Is Working Day] column.

I don't get any error after creating the measure however it just shows a blank. Could this be because there is no relationship between my Date table and my Run Rate table?

Thanks a lot for your help!

Nick

[Icey]

Hi @Nick2358 ,

Relationship between your Date table and Run Rate table is needed. Please create it.

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Nick2358]

Hi @Icey 

I'm not sure how to create a relationship between a date table and a table where I have no date?

Thanks,

[Icey]

Hi @Nick2358 ,

You may need a date column in your fact table. If not, how do you judge which date "Actual" and "Target" record are for? For example, Actual = 4 and Target = 7 is based on Nov 4th.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.