Running total not giving correct answer

[Devon-Yeager]

I'm trying to get the running sum of hours of a measure that ultimately includes a calculation of an average. 

Power BI isn't giving me the correct answer. 

		Correct Hours	Power BIs answer
	4. Available Hours	5. Running Sum
2024/Dec	730.86	730.86	730.86
2025/Jan	766.72	1497.58	1497.59
2025/Feb	689.6	2187.18	2186.64
2025/Mar	731.61	2918.79	2918.79
2025/Apr	938.94	3857.73	3850.01
2025/May	773.66	4631.39	4623.77
2025/Jun	710.72	5342.11	5333.61
2025/Jul	671.75	6013.86	6000.06
2025/Aug	562.42	6576.28	6562.02
2025/Sep	685.42	7261.7	7247.52
2025/Oct	845.69	8107.39	8086.51
2025/Nov	539.07	8646.46	8638.06
2025/Dec	778.5	9424.96	9413.33
2026/Jan	532.49	9957.45	9948.18
2026/Feb	531.27	10488.72	10483.54
2026/Mar	643.04	11131.76	11123.57
2026/Apr	0	11131.76	10799.53
2026/May	0	11131.76	10313.08

There are a string of formulas used to make 5. Running sum

5. Running sum = CALCULATE([4. Available Hours],filter(ALLSELECTED('Date Table'),'Date Table'[Date] <=max('Date Table'[Date])))

4. Available Hours = if([3. Total Available Hours]-sum('7 - SharePoint - EPGB Demand Plan'[Total EPGB Demand])-sum('8 - Graph table'[Urgent New Work])<0,0,[3. Total Available Hours]-sum('7 - SharePoint - EPGB Demand Plan'[Total EPGB Demand])-sum('8 - Graph table'[Urgent New Work]))

3. Total Available Hours = round([What If Employee Count]*sum('4 - Working Hours per Day'[Working Hours Per Day])*([1. Working Days]-[2. Time Off Days per emp]),2)

Here's where I think the issue starts:

What If Employee Count = average('3 - D&A Employees by month V2'[EmployeeCount]) + SELECTEDVALUE('9 - What If Add FTE'[Additional FTE])

I want to be able to dynamically change the employee count so I can determine how many months of capacity we can gain if we add an employee or know what happens if we lose an employee. 

Also considered in the EmployeeCount are times when an employee is gone for more than a month at a time.

If you'd like to see the pbix so you can explore, please let me know how I can attach it. 

Any help you can provide to help me get the correct running sum would be very much appreciated. 

Thanks Devon

[Anonymous]

Hi @Devon-Yeager ,

Please provide sample PBIX files so we can better understand and help you.
Refer to the following link on how to upload a PBIX file.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Devon-Yeager]

Thank you for the instructions. Another person provided a solution so I don't need to upload the pbix. I will keep this in mind for other needs. 

[Ashish_Mathur]

Hi,

This pattern should work

1. Create a Calendar Table with calculated column formulas of Year, Month name and Month number
2. Sort the Month name column by the Month number
3. Create a relationship (Many to One and single) from the Date column of your fact table to the Date column of the Calendar Table
4. Write this measure

Measure = calculate([4. Available hours],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

Hope this helps.

[Devon-Yeager]

Thank you Ashish for providing this solution. It's still not providing the correct results though. There are many months in the data before the current month. I need the measure to only consider this month and forward. 

I also tried adding a condition to the measure: 'Date Table'[* Before end of last month] = "No", this provided the same results as the original measure I created. 

Here are the new results using your measure:

Year Month	4. Available Hours	5. Running sum	Measure
2024/Dec	730.86	730.86	153765.46
2025/Jan	766.72	1497.59	154532.19
2025/Feb	689.6	2186.64	155221.79
2025/Mar	731.61	2918.79	155953.39
2025/Apr	938.94	3850.01	160290.6
2025/May	773.66	4623.77	160497.06
2025/Jun	710.72	5333.61	160801.39
2025/Jul	671.75	6000.06	163291.95
2025/Aug	562.42	6562.02	165273.29
2025/Sep	685.42	7247.52	167093.78
2025/Oct	845.69	8086.51	168861.09
2025/Nov	539.07	8638.06	170187.08
2025/Dec	778.5	9413.33	171617.13
2026/Jan	532.49	9948.18	172713.49
2026/Feb	531.27	10483.54	173735.65
2026/Mar	643.04	11123.57	174801.06
2026/Apr	0	10799.53	174852.79
2026/May	0	10313.08	174700.17

[Ashish_Mathur]

Follow the instructions mentioned in my previuos mail and revise the measure to

Measure = calculate([4. Available hours],datesbetween(calendar[date],eomonth(today(),-1)+1,max(calendar[date])))

Hope this helps.

[Devon-Yeager]

Thank you for the additional measure to try. It's still giving me the same answer. 

I really think the issue starts at the What If Employee Count measure ( = average('3 - D&A Employees by month V2'[EmployeeCount]) + SELECTEDVALUE('9 - What If Add FTE'[Additional FTE])).

I think the "average" in the measure is causing unexpected results in the downstream measures that use it. 

I think I need to add something to the What If Employee Count measure but I'm not sure what. If you have any advice on that, I'd surely appreciate it. 

Thanks!

[Irwan · Accepted solution]

hello @Devon-Yeager 

if i am not misunderstood the discussion, the problem is how to calculate running sum from a measure.

next the [Available Hours] is a form of measure from another calculation.

please check if this accomodate your expected result (please ignore the index as it works only as sorting in table visual).

i think the [5. Running Sum] above should be matched to your correct hours.

create a new measure for calculating Running Sum

5. Running Sum = 
SUMX(
    FILTER(
        ALL('Table'),
        'Table'[Date]<=MAX('Table'[Date])
    ),
    [Available Hours]
)

i made the [Available Hours] above as a measure because your original data has [Available Hours] as measure.

i assumed you have expected result for calculations before [Available Hours].

Hope this will help.

Thank you.

[Devon-Yeager]

Thank you so much! This does work! I really appreciate the help!

[Irwan]

Hello @Devon-Yeager 

Glad it works.

Thank you.

[Ashish_Mathur]

You are welcome.  Without data to work with and the expected result, i cannot help.

[Devon-Yeager]

I included the expected result in the original post. 

I have a pbix I can share but I'm not sure how to attach it to this post. If you know how, please let me know. 

[Ashish_Mathur]

I do not want to see the expected result of the running total - the formula which i shared with you previously will defenitely work.  I want to see the expected result of the What-If employee count measure - the one which you think is failing.

[Devon-Yeager]

Maybe my additional comment I left earlier this week that includes the What If Employee Count and 3 other columns could be helpful. If not, I understand. Thanks. 

[Devon-Yeager]

Here are a couple more columns of data to help tell the story

				Correct Hours	Power BIs answer
	What If Employee Count	3. Total Available Hours	4. Available Hours	5. Running Sum
2024/Dec	9.18	1006.86	730.86	730.86	730.86
2025/Jan	9.18	1048.72	766.72	1497.58	1497.59
2025/Feb	9.18	998.6	689.6	2187.18	2186.64
2025/Mar	9.18	1036.61	731.61	2918.79	2918.79
2025/Apr	10.18	1217.94	938.94	3857.73	3850.01
2025/May	9.18	1042.66	773.66	4631.39	4623.77
2025/Jun	9.18	945.72	710.72	5342.11	5333.61
2025/Jul	10.18	1139.75	671.75	6013.86	6000.06
2025/Aug	10.18	1030.42	562.42	6576.28	6562.02
2025/Sep	10.18	1132.42	685.42	7261.7	7247.52
2025/Oct	10.18	1260.69	845.69	8107.39	8086.51
2025/Nov	10.18	954.07	539.07	8646.46	8638.06
2025/Dec	10.18	1193.5	778.5	9424.96	9413.33
2026/Jan	10.18	1113.49	532.49	9957.45	9948.18
2026/Feb	10.18	1112.27	531.27	10488.72	10483.54
2026/Mar	10.18	1224.04	643.04	11131.76	11123.57
2026/Apr	10.18	1217.94	0	11131.76	10799.53
2026/May	10.18	1107.38	0	11131.76	10313.08