Forum Discussion

Clemboa's avatar
Clemboa
New Member
8 years ago

Running total by hour

Hi,

 

I need to monitor cummulative footfall during a day.

 

For example, the footfall was 9 between 0900 and 1000, and 260 between 1000 and 1100, I need the measure for 1000 and 1100 to be 269.

 

I have a field TrxLine[Hour] and a measure called TrxLine[Footfall].

 

I have created the following DAX function, however, it just returns the hourly totals and is not summing up by hour.

 

 

 

FootfallHTD =
CALCULATE (
SUM ( TrxLine[footfall]),
FILTER ( ALL ( TrxLine[RedemptionHr]),
TrxLine[RedemptionHr] <= MAX ( TrxLine[RedemptionHr]) && TrxLine[RedemptionDate] = TrxLine[RedemptionDate]
))

 

The formula compiles, 

 

Any ideas greatly appreciated.

 

Thanks.

1 Reply

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi Clemboa,

     

    Could you try using the formula below to create new measure to see if it works in your scenario? :smileyhappy:

    FootfallHTD =
CALCULATE (
    SUM ( TrxLine[footfall] ),
    FILTER (
        ALL ( TrxLine ),
        TrxLine[RedemptionHr] <= MAX ( TrxLine[RedemptionHr] )
            && TrxLine[RedemptionDate] = TrxLine[RedemptionDate]
    )
)

     

    Regards