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!
