topic Re: Cumulative Sum within Multiple Date Ranges in DAX Commands and Tips

Cumulative Sum within Multiple Date Ranges

MAVIE — Wed, 20 Jul 2022 14:21:05 GMT

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
1	Iteration 2022.01	03-01-2022	23-01-2022	[List]	15	22,4667
2	Iteration 2022.02	24-01-2022	20-02-2022	[List]	20	22,3
3	Iteration 2022.03	21-02-2022	20-03-2022	[List]	20	32,6
4	Iteration 2022.04	21-03-2022	17-04-2022	[List]	20	46,5875
5	Iteration 2022.05	18-04-2022	15-05-2022	[List]	20	47,4625
6	Iteration 2022.06	16-05-2022	12-06-2022	[List]	20	49,9625
7	Iteration 2022.07	13-06-2022	03-07-2022	[List]	15	64,4333
8	Iteration 2022.08	04-07-2022	24-07-2022	[List]	15	97,7
9	Iteration 2022.09	25-07-2022	21-08-2022	[List]	20	25,625
10	Iteration 2022.10	22-08-2022	18-09-2022	[List]	20	24,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

Re: Cumulative Sum within Multiple Date Ranges

lbendlin — Sat, 23 Jul 2022 02:57:58 GMT

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

Re: Cumulative Sum within Multiple Date Ranges

MAVIE — Mon, 25 Jul 2022 06:42:32 GMT

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
    )

Re: Cumulative Sum within Multiple Date Ranges

MAVIE — Mon, 25 Jul 2022 11:38:36 GMT

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