Running Total Help

[MichelleRoberts]

Hello!

I have a data set that looks like this:

Where

Demand = WeeklyTriggerHrs + TotalHoursCurrent + TotalHrsPastDue

AvgHoursPW = (Sum of run_mch_hrs_per_day)*6

Demand-HoursPW = Demand - AvgHoursPW

I'm having trouble with the RunningTotalDemand-HrsPWHrs. As you can see, it's not creating a running total. Here's the formula I've got so far:

RunningTotalDemand-HrsPWHrs =

    CALCULATE(

        [Demand-HoursPW],

        FILTER(

            ALL('Calendar'),

            'Calendar'[Date] <= MAX('Calendar'[Date])

        )

    )

Can someone see where my error is? I also don't want it to add anything if there is no demand and the date is in the past, but I do want it to add if there is no demand and the date is current.

[BeaBF]

@MichelleRoberts Hi!

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],
0
)
)
)

If it's ok please accept my answer as solution, instead, paste me some sample data plis

BBF

[MichelleRoberts]

It's still not calculating correctly. I exported the data to Excel so I could calculate it and compare the two. See below.

[BeaBF]

@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

[MichelleRoberts]

I now get this as a result:

[BeaBF]

@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

[MichelleRoberts]

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

[BeaBF]

@MichelleRoberts  based on this data, this formula works for me:

CumulativeDemandHoursPW =

VAR CurrentYearWeek = SELECTEDVALUE('Table'[Year-Week])

RETURN

CALCULATE(

    SUM('Table'[Demand-HoursPW]),

    FILTER(

        ALL('Table'),

        'Table'[Year-Week] <= CurrentYearWeek

    )

)

BBF

[MichelleRoberts]

I see that it works for you but it's still not working for me. I'm going to try something else.

[BeaBF]

@MichelleRoberts can you upload the pbix in drive and share the link? so that i can work on your version.

BBF