Forum Discussion
Running total not giving correct answer
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.
Hi,
This pattern should work
- Create a Calendar Table with calculated column formulas of Year, Month name and Month number
- Sort the Month name column by the Month number
- Create a relationship (Many to One and single) from the Date column of your fact table to the Date column of the Calendar Table
- Write this measure
Measure = calculate([4. Available hours],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))
Hope this helps.
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 |
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.
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!
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.