Forum Discussion

MAVIE's avatar
MAVIE
Helper I
4 years ago
Solved

Cumulative Sum within Multiple Date Ranges

Hi All, I am trying to create a measure which calculates a cumulative sum over dates, with different values for different date ranges, but have gotten completely stuck.  My data looks like the fo...
  • MAVIE's avatar
    MAVIE
    4 years ago

    Turns out that using the  following method to count days in current context was very inefficient: 

    CALCULATE (
        COUNTROWS ( DimDate ),
        DATESBETWEEN ( DimDate[Date], CurrentStart, EndOfPeriod ),
        DimDate[IsWorkDay] = TRUE ()
    )

     I have now instead used the new DAX function NETWORKDAYS, which has fixed the performance issue. 
    The final measure therefore looks like this: 

    VAR CurrentDate = MAX ( DimDate[Date] )
    VAR HoursPerDay =
        ADDCOLUMNS (
            DimIterations,
            "HoursPerDayCases",
            CALCULATE (
                DIVIDE( SUM(FactCases_T1[Estimate] ), MAX( DimIterations[DaysInIteration] ), 0 ),
                USERELATIONSHIP( DimIterations[Id], FactCases_T1[IterationId] )
            )
        )

    VAR     Result = 
        SUMX (
            HoursPerDay, 
            VAR CurrentStart = DimIterations[StartDate]
            VAR CurrentEnd = DimIterations[EndDate]
            VAR DaysGone =
                IF ( 
                    CurrentDate > CurrentStart, 
                    IF (
                        CurrentDate > CurrentEnd,
                        NETWORKDAYS( CurrentStart, CurrentEnd, 1 ),
                        NETWORKDAYS( CurrentStart, CurrentDate, 1 )
                    ),
                    0
                )
            RETURN
                [HoursPerDayCases] * DaysGone
        )

    RETURN
        Result
MAVIE's avatar
MAVIE
Helper I
4 years ago

I am excluding weekends yes.
I have built the following measure which gives me the correct result, however it is quite slow. Do you have any suggestions for imporvements? 

VAR CurrentDate = MAX ( DimDate[Date] )
VAR Result = 
    SUMX(
        HoursPerDayCases, 
        VAR CurrentStart = DimIterations[StartDate]
        VAR CurrentEnd = DimIterations[EndDate]
        VAR EndOfPeriod =
            IF (
                CurrentDate >= CurrentEnd,
                CurrentEnd,
                CurrentDate
            )
        VAR DaysGone =
            MAX (
                0,
                CALCULATE (
                    COUNTROWS ( DimDate ),
                    DATESBETWEEN ( DimDate[Date], CurrentStart, EndOfPeriod ),
                    DimDate[IsWorkDay] = TRUE ()
                )
            )
        RETURN
            [HoursPerDay] * DaysGone
    )
MAVIE's avatar
MAVIE
Helper I
4 years ago

Turns out that using the  following method to count days in current context was very inefficient: 

CALCULATE (
    COUNTROWS ( DimDate ),
    DATESBETWEEN ( DimDate[Date], CurrentStart, EndOfPeriod ),
    DimDate[IsWorkDay] = TRUE ()
)

 I have now instead used the new DAX function NETWORKDAYS, which has fixed the performance issue. 
The final measure therefore looks like this: 

VAR CurrentDate = MAX ( DimDate[Date] )
VAR HoursPerDay =
    ADDCOLUMNS (
        DimIterations,
        "HoursPerDayCases",
        CALCULATE (
            DIVIDE( SUM(FactCases_T1[Estimate] ), MAX( DimIterations[DaysInIteration] ), 0 ),
            USERELATIONSHIP( DimIterations[Id], FactCases_T1[IterationId] )
        )
    )

VAR Result = 
    SUMX (
        HoursPerDay, 
        VAR CurrentStart = DimIterations[StartDate]
        VAR CurrentEnd = DimIterations[EndDate]
        VAR DaysGone =
            IF ( 
                CurrentDate > CurrentStart, 
                IF (
                    CurrentDate > CurrentEnd,
                    NETWORKDAYS( CurrentStart, CurrentEnd, 1 ),
                    NETWORKDAYS( CurrentStart, CurrentDate, 1 )
                ),
                0
            )
        RETURN
            [HoursPerDayCases] * DaysGone
    )

RETURN
    Result