Forum Discussion

MichelleRoberts's avatar
MichelleRoberts
Regular Visitor
2 years ago

Running Total Help

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 - A...
BeaBF's avatar
BeaBF
Super User
2 years ago

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's avatar
MichelleRoberts
Regular Visitor
2 years ago

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

  • BeaBF's avatar
    BeaBF
    Super User
    2 years ago

    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's avatar
      MichelleRoberts
      Regular Visitor
      2 years ago

      I now get this as a result:

      • BeaBF's avatar
        BeaBF
        Super User
        2 years ago

        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