cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

1 ACCEPTED SOLUTION
Helper I

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 3
Super User

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

Helper I

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    )`
Helper I

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`

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors