Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors