Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Running Cumulative based on hours

  I need your help on a DAX formula to calculate the third column 'CumulativeForTheDay' that's is required to be a running total. The required calculation will be for 24 hours starting at '01:...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous,

 

You can try to use following measure formula, I write formula to use date part as condition to limit cumulative date range:

 

Measure =
VAR currDate =
    MAX ( Table[TimeStamp] )
RETURN
    CALCULATE (
        SUM ( Table[FlowAtGivenInterval] ),
        FILTER (
            ALLSELECTED ( Table ),
            OR (
                DATEVALUE ( Table[TimeStamp] ) = DATEVALUE ( currDate ),
                Table[TimeStamp]
                    = DATEVALUE ( currDate ) + 1
            )
                && AND ( Table[TimeStamp] > DATEVALUE ( currDate ), Table[TimeStamp] < currDate )
        )
    )

 

 

Regards,

Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hello Xiaoxin,

     

    Thanks for your time. Your code is very close to what I need, it just need to provide a value at '01:00' AM as highlighted in the screenshot which will be the same value from the 'FlowAtGivenInterval' column - 78.66 - in this specific date. You can also notice that no value is available at midnight which I want its value to be accounted and included for the cumulative for this day.

     

    Thanks again for your help.

     

    Emad

     

    RunningTotal =
    VAR currDate =
    MAX ( Daily_Methane_1[TimeStamp] )
    RETURN
    CALCULATE (
    SUM ( Daily_Methane_1[FlowAtGivenInterval] ),
    FILTER (
    ALLSELECTED ( Daily_Methane_1 ),
    OR (
    DATEVALUE ( Daily_Methane_1[TimeStamp] ) = DATEVALUE ( currDate ),
    Daily_Methane_1[TimeStamp]
    = DATEVALUE ( currDate ) + 1
    )
    && AND ( Daily_Methane_1[TimeStamp] > DATEVALUE ( currDate ), Daily_Methane_1[TimeStamp] < currDate )
    )
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Anonymous,

       

      Can you please provide some sample data so that we can test and modify dax formula in power bi side?

      Regards,

      Xiaoxin Sheng

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hello Xiaoxin,

         

        Would you please share your email so I can send you a sample file as it looks like I can't upload it here.

         

        Thanks,

         

        Emad