Forum Discussion

Devon-Yeager's avatar
Devon-Yeager
Frequent Visitor
1 year ago
Solved

Running total not giving correct answer

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 HoursPower BIs answer
 4. Available Hours5. Running Sum 
2024/Dec730.86730.86730.86
2025/Jan766.721497.581497.59
2025/Feb689.62187.182186.64
2025/Mar731.612918.792918.79
2025/Apr938.943857.733850.01
2025/May773.664631.394623.77
2025/Jun710.725342.115333.61
2025/Jul671.756013.866000.06
2025/Aug562.426576.286562.02
2025/Sep685.427261.77247.52
2025/Oct845.698107.398086.51
2025/Nov539.078646.468638.06
2025/Dec778.59424.969413.33
2026/Jan532.499957.459948.18
2026/Feb531.2710488.7210483.54
2026/Mar643.0411131.7611123.57
2026/Apr011131.7610799.53
2026/May011131.7610313.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
Need Help
  • Irwan's avatar
    Irwan
    1 year ago

    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.

14 Replies

  • Devon-Yeager's avatar
    Devon-Yeager
    Frequent Visitor
    1 year ago

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

        Correct HoursPower BIs answer
     What If Employee Count3. Total Available Hours4. Available Hours5. Running Sum 
    2024/Dec9.181006.86730.86730.86730.86
    2025/Jan9.181048.72766.721497.581497.59
    2025/Feb9.18998.6689.62187.182186.64
    2025/Mar9.181036.61731.612918.792918.79
    2025/Apr10.181217.94938.943857.733850.01
    2025/May9.181042.66773.664631.394623.77
    2025/Jun9.18945.72710.725342.115333.61
    2025/Jul10.181139.75671.756013.866000.06
    2025/Aug10.181030.42562.426576.286562.02
    2025/Sep10.181132.42685.427261.77247.52
    2025/Oct10.181260.69845.698107.398086.51
    2025/Nov10.18954.07539.078646.468638.06
    2025/Dec10.181193.5778.59424.969413.33
    2026/Jan10.181113.49532.499957.459948.18
    2026/Feb10.181112.27531.2710488.7210483.54
    2026/Mar10.181224.04643.0411131.7611123.57
    2026/Apr10.181217.94011131.7610799.53
    2026/May10.181107.38011131.7610313.08
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    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's avatar
      Devon-Yeager
      Frequent Visitor
      1 year ago

      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 Month4. Available Hours5. Running sumMeasure
      2024/Dec730.86730.86153765.46
      2025/Jan766.721497.59154532.19
      2025/Feb689.62186.64155221.79
      2025/Mar731.612918.79155953.39
      2025/Apr938.943850.01160290.6
      2025/May773.664623.77160497.06
      2025/Jun710.725333.61160801.39
      2025/Jul671.756000.06163291.95
      2025/Aug562.426562.02165273.29
      2025/Sep685.427247.52167093.78
      2025/Oct845.698086.51168861.09
      2025/Nov539.078638.06170187.08
      2025/Dec778.59413.33171617.13
      2026/Jan532.499948.18172713.49
      2026/Feb531.2710483.54173735.65
      2026/Mar643.0411123.57174801.06
      2026/Apr010799.53174852.79
      2026/May010313.08174700.17
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        1 year ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Devon-Yeager
      Frequent Visitor
      1 year ago

      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.