Forum Discussion
Running Total Help
MichelleRoberts adjusted:
RunningTotalDemand-HrsPWHrs =
VAR CurrentDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
SUMX(
FILTER(
ALL('Calendar'),
'Calendar'[Date] <= CurrentDate
),
IF(
[Demand] > 0 || 'Calendar'[Date] = CurrentDate,
[Demand-HoursPW],
0
)
),
ALLEXCEPT('Calendar', 'Calendar'[Date])
)
BBF
I now get this as a result:
MichelleRoberts can you paste the data? so that i can do copy paste into a power bi and do some tests.
meanwhile try with:
RunningTotalDemand-HrsPWHrs =
VAR CurrentDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
SUMX(
FILTER(
ALL('Calendar'),
'Calendar'[Date] <= CurrentDate
),
IF(
[Demand] > 0 || 'Calendar'[Date] = CurrentDate,
[Demand-HoursPW],
BLANK() -- Use BLANK() instead of 0 to avoid adding zero values
)
)
)BBF
It's still not working. Here's the data:
Year-Week wc Demand AvgHoursPW Demand-HoursPW RunningTotalDemand-HrsPWHrs WeeklyTriggerHrs TotalHrsCurrent TotalHrsPastDue Sum of run_mch_hrs_per_day 2023-24 CELL A 0.16 14.85874 -14.6987 0 16.45821 0.16 2.476457 2024-01 CELL A 17.29821 14.85874 2.439471 -14.6987 16.45821 0.84 2.476457 2024-20 CELL A 0.72 14.85874 -14.1387 -28.8375 16.45821 0.72 2.476457 2024-21 CELL A 1.48 14.85874 -13.3787 -42.2162 16.45821 1.48 2.476457 2024-22 CELL A 7.68 14.85874 -7.17874 -68.3937 16.45821 7.68 2.476457 2024-23 CELL A 6.96 14.85874 -7.89874 -92.2312 16.45821 6.96 2.476457 2024-24 CELL A 25.33 14.85874 10.47126 -128.737 16.45821 25.33 2.476457 2024-25 CELL A 36.6 14.85874 21.74126 -179.022 16.45821 36.6 2.476457 2024-26 CELL A 5.7 14.85874 -9.15874 -237.107 16.45821 5.7 2.476457 2024-27 CELL A 119.25 14.85874 104.3913 -235.744 16.45821 119.25 2.476457 2024-28 CELL A 697.2 14.85874 682.3413 -145.257 16.45821 697.2 2.476457 2024-29 CELL A 466.86 14.85874 452.0013 -100.271 16.45821 466.86 2.476457 2024-30 CELL A 349.3 14.85874 334.4413 -54.3347 16.45821 349.3 2.476457 2024-31 CELL A 3476.808 14.85874 3461.949 0 16.45821 1780.55 1679.8 2.476457 2024-32 CELL A 5114.928 14.85874 5100.069 0 16.45821 5098.47 2.476457 2024-33 CELL A 9450.058 14.85874 9435.199 0 16.45821 9433.6 2.476457 2024-34 CELL A 7518.218 14.85874 7503.359 0 16.45821 7501.76 2.476457 2024-35 CELL A 17233.76 14.85874 17218.9 0 16.45821 17217.3 2.476457 2024-36 CELL A 5676.538 14.85874 5661.679 0 16.45821 5660.08 2.476457 2024-37 CELL A 4802.778 14.85874 4787.919 0 16.45821 4786.32 2.476457 2024-38 CELL A 46648.5 14.85874 46633.64 0 16.45821 46632.04 2.476457 2024-39 CELL A 22730.06 14.85874 22715.2 0 16.45821 22713.6 2.476457 2024-40 CELL A 4930.058 14.85874 4915.199 0 16.45821 4913.6 2.476457 2024-41 CELL A 7827.078 14.85874 7812.219 0 16.45821 7810.62 2.476457 MichelleRoberts based on this data, this formula works for me:
CumulativeDemandHoursPW =VAR CurrentYearWeek = SELECTEDVALUE('Table'[Year-Week])RETURNCALCULATE(SUM('Table'[Demand-HoursPW]),FILTER(ALL('Table'),'Table'[Year-Week] <= CurrentYearWeek))BBF