Time to Complete Budget Allocation

DAX DAX Calculations
6

Hi @Simon1,

This will be pretty easy if you set it up in Power Query, it’s similar to POTW #4.
Consequence will be an increase in data model size but calculating it with DAX isn’t cheap either, although there might be some optimizations possible…

Anyway this is how far I got.
Note that I had to change the relationships in the model view to inactive on both dates.

Job Allocated Time = 
VAR _SDate = MINX( VALUES('Job Budget'[[Job]] Start Date] ), [[Job]] Start Date] )
VAR _DDate = MAXX( VALUES('Job Budget'[[Job]] Due Date] ), [[Job]] Due Date] )
VAR vDates = SELECTCOLUMNS( FILTER( VALUES( Dates ), Dates[Date] >= _SDate && Dates[Date] <= _DDate && Dates[IsWorkingDay] = TRUE() ), "Date", [Date] )
VAR vJobs = VALUES( 'Job Budget'[[Job]] Job No.] )
VAR vTable =
    ADDCOLUMNS( 
        GENERATEALL( vDates, vJobs),
        "@Allocation",
            VAR myJob = [[Job]] Job No.] 
            VAR _JobS = CALCULATE( MINX( VALUES('Job Budget'[[Job]] Start Date] ), [[Job]] Start Date] ), 'Job Budget'[[Job]] Job No.] = myJob )
            VAR _JobD = CALCULATE( MAXX( VALUES('Job Budget'[[Job]] Due Date] ), [[Job]] Due Date] ), 'Job Budget'[[Job]] Job No.] = myJob )
            VAR DayInContext = COUNTROWS(Dates)
            VAR BudgetedJobDays =  CALCULATE( SUM('Job Budget'[Job Budget Days]), 'Job Budget'[[Job]] Job No.] = myJob )
            VAR BudgetedTime = CALCULATE( SUM('Job Budget'[Estimated Time (HRS)]), 'Job Budget'[[Job]] Job No.] = myJob )
        RETURN
        CALCULATE( IF( HASONEVALUE( Dates[Date] ),
            DIVIDE( DayInContext, BudgetedJobDays, 0 ) * BudgetedTime, 0
        ),  FILTER( VALUES( Dates ), Dates[Date] >= _JobS && Dates[Date] <= _JobD && Dates[IsWorkingDay] = TRUE() ))
    )
RETURN

    SUMX( vTable, [@Allocation] )

.
With this result.

image
image644×707 53.5 KB

Here’s your sample. DatesBetweenModel.pbix (142.8 KB)
I hope this is helpful.

3 Likes