cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MAVIE
Helper I
Helper I

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. 

MAVIE_0-1658326736536.png

 



Any help would be greatly appreciated

1 ACCEPTED SOLUTION

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

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

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

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
    )

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

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors