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 following: 
 

Id   Title                      

StartDate       

Enddate         TeamId  DaysInIteration  HoursPerDay  
1Iteration 2022.0103-01-202223-01-2022[List]1522,4667
2Iteration 2022.0224-01-202220-02-2022[List]2022,3
3Iteration 2022.0321-02-202220-03-2022[List]2032,6
4Iteration 2022.0421-03-202217-04-2022[List]2046,5875
5Iteration 2022.0518-04-202215-05-2022[List]2047,4625
6Iteration 2022.0616-05-202212-06-2022[List]2049,9625
7Iteration 2022.0713-06-202203-07-2022[List]1564,4333
8Iteration 2022.0804-07-202224-07-2022[List]1597,7
9Iteration 2022.0925-07-202221-08-2022[List]2025,625
10Iteration 2022.1022-08-202218-09-2022[List]2024,85

Currently HoursPerDay, is calculated within the measure as such: 

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

 
The measure then needs to cumulatively summarize HoursPerDay, for the related daterange between StartDate and EndDate. 
The date ranges are not necessarily sequential and can be overlapping. 

I also have a date table, however only with inactive relationships to my iterations table. 

 



Any help would be greatly appreciated

  • 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

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Your date ranges do not match the "DaysInIteration"  values.  Are you excluding weekends and holidays?

    • 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