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 - AvgHoursPW

 

I'm having trouble with the RunningTotalDemand-HrsPWHrs. As you can see, it's not creating a running total. Here's the formula I've got so far:

RunningTotalDemand-HrsPWHrs =
    CALCULATE(
        [Demand-HoursPW],
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] <= MAX('Calendar'[Date])
        )
    )
 
Can someone see where my error is? I also don't want it to add anything if there is no demand and the date is in the past, but I do want it to add if there is no demand and the date is current.

 

9 Replies

  • 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