DAX Projection of Hours

[Anonymous]

All,

I'm trying to write a simple projection of hours over time.  For example, based on certain variables, at what date in the future would 200 hours be consumed.  This is required to be plotted on a chart.

I whipped up the expression at the end of this post that works fine in DAX Studio.  However, I am having trouble displaying it when I plot it against DimDate[Date] in Power BI.  See below:

 

Note, to replicate the above screenshot, simply use the below expression in any model with a date dimension and: (1) replace the reference to your DimDate table name; and (2) replace the returned value with the one in comments for Power BI (keep it as-is when using DAX Studio):

EVALUATE

// VARIABLES
	VAR __SelectedStartDate = DATE ( 2019, 03, 01 )
	VAR __Headcount = 2 
	VAR __FteHours = 8
	VAR __FudgeFactor = 0.20
	VAR __TotalHours = 200

// Requires any DimDate, so long as it respects the variable __SelectedStartDate
VAR __BaseTable =
    CALCULATETABLE (
        'DimDate',
        ALL ( DimDate ),
        FILTER (
            'DimDate',
            NOT WEEKDAY ( 'DimDate'[Date] ) IN { 1, 7 } && [Date] >= __SelectedStartDate
        )
    )
    
// Projects hours forward for every date in the future
VAR __HoursProjection =
    ADDCOLUMNS (
        __BaseTable,
        "Hours Consumed",
        VAR __ProjectedHours =
            CALCULATE (
                COUNTROWS ( 'DimDate' ),
                FILTER ( ALLSELECTED ( 'DimDate' ), DimDate[Date] <= EARLIER ( DimDate[Date] ) )
            ) * __FteHours * __HeadCount * ( 1 - __FudgeFactor )
        RETURN
        // This logic prevents the projection from exceeding variable __TotalHours
            IF (
                __ProjectedHours <= __TotalHours,
                __ProjectedHours,
                IF ( __ProjectedHours - __TotalHours < __FteHours, __TotalHours, 0 ) // Set the last date to be equal to __TotalHours else 0.            
            )
    )
RETURN
    __HoursProjection // To return scalar value, replace with an aggregation, e.g., "MAXX( __HoursProjection, [Hours Consumed] )" 

 Any thoughts on how to evaluate it correctly against DimDate[Date] instead of repeating values?

[GilbertQ]

Hi @Anonymousare you looking for the cumulative Projection for each day?

If so you could try this following measure

Projection Running Total = 
CALCULATE (
    [Projection],
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Parts Sales Line'[Sales Order Created Date] )
    )
)

[Anonymous]

Thanks for the response GilbertQ. That is part of what I’m looking for and, as you can see above, I have already implemented that logic into the expression. Are you able to plug my expression into DAX Studio to assist in finding a solution, i.e., return a scalar value that retains data lineage to DimDate?

[GilbertQ]

hi @Anonymous

Please try this below.

EVALUATE

// VARIABLES
	VAR __SelectedStartDate = DATE ( 2019, 03, 01 )
	VAR __Headcount = 2 
	VAR __FteHours = 8
	VAR __FudgeFactor = 0.20
	VAR __TotalHours = 200

// Requires any DimDate, so long as it respects the variable __SelectedStartDate
VAR __BaseTable =
    CALCULATETABLE (
        'DimDate',
        ALL ( DimDate ),
        FILTER (
            'DimDate',
            NOT WEEKDAY ( 'DimDate'[Date] ) IN { 1, 7 } && [Date] >= __SelectedStartDate
        )
    )
    
// Projects hours forward for every date in the future
VAR __HoursProjection =
    ADDCOLUMNS (
        __BaseTable,
        "Hours Consumed",
        VAR __ProjectedHours =
            CALCULATE (
                COUNTROWS ( 'DimDate' ),
                FILTER ( ALLSELECTED ( 'DimDate' ), DimDate[Date] <= EARLIER ( DimDate[Date] ) )
            ) * __FteHours * __HeadCount * ( 1 - __FudgeFactor )
        RETURN
        // This logic prevents the projection from exceeding variable __TotalHours
            IF (
                __ProjectedHours <= __TotalHours,
                __ProjectedHours,
                IF ( __ProjectedHours - __TotalHours < __FteHours, __TotalHours, 0 ) // Set the last date to be equal to __TotalHours else 0.            
            )
    )
RETURN
CALCULATE (
    [__HoursProjection],
    FILTER (
        ALL ( 'DimDate '[Date] ),
        'DimDate '[Date] <= MAX ( 'DimDate '[Date] )
    )
)
    // To return scalar value, replace with an aggregation, e.g., "MAXX( __HoursProjection, [Hours Consumed] )" 

[Anonymous]

I'm having trouble trying to understand how your solution would work, @GilbertQ.  Do you mind explaining your thought process?

[Anonymous · Accepted solution]

Solution below:

MeasureName = 
// VARIABLES
VAR __SelectedStartDate = CALCULATE( MIN( 'DimDate'[Date] ), ALLSELECTED( DimDate[Date] ) )//DATE ( 2019, 03, 01 )
VAR __Headcount = 2
VAR __FteHours = 8
VAR __FudgeFactor = 0.20
VAR __TotalHours = 200

VAR __BaseTable =
    ADDCOLUMNS (
        DimDate,
        "Hours Consumed",
        VAR __ProjectedHours =
            CALCULATE (
                COUNTROWS ( DimDate ),
                FILTER (
                    ALL ( 'DimDate' ),
                    NOT WEEKDAY ( 'DimDate'[Date] ) IN { 1, 7 }
                        && [Date] >= __SelectedStartDate
                        && [Date] <= EARLIER ( DimDate[Date] )
                )
            ) * __FteHours * __HeadCount * ( 1 - __FudgeFactor )
        RETURN
            IF (
                __ProjectedHours <= __TotalHours,
                __ProjectedHours,
                IF ( __ProjectedHours - __TotalHours < __FteHours, __TotalHours, BLANK () )
            )
    )
RETURN
SUMX( __BaseTable, [Hours Consumed] )

[GilbertQ]

Thanks for posting your solution.

It does appear that you have optimized the code really well.