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
Solved! Go to 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
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
User | Count |
---|---|
93 | |
36 | |
34 | |
18 | |
13 |
User | Count |
---|---|
95 | |
29 | |
27 | |
17 | |
16 |