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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello Xiaoxin,

       

      Please check this test report. Thanks for your help.

       

      Emad.

       

      Test Report

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous,

         

        I add a condition to summarize yesterday total on 12am, you can use following measure if it works for your requirement:

        RunningTotal 2 = 
VAR currDate =
    MAX ( Daily_Methane_1[TimeStamp] )
RETURN
    IF (
        TIMEVALUE ( currDate ) <> TIME(0,0,0),
        CALCULATE (
            SUM ( Daily_Methane_1[FlowAtGivenInterval] ),
            FILTER (
                ALLSELECTED ( Daily_Methane_1 ),
                AND (
                    Daily_Methane_1[TimeStamp] >= DATEVALUE ( currDate ),
                    Daily_Methane_1[TimeStamp] < currDate
                )
            )
        ),
        CALCULATE (
            SUM ( Daily_Methane_1[FlowAtGivenInterval] ) + 0,
            FILTER (
                ALLSELECTED ( Daily_Methane_1 ),
                DATEVALUE ( Daily_Methane_1[TimeStamp] )
                    = DATEVALUE ( currDate ) - 1
            )
        )
    )

         

        Regards,

        Xiaoxin Sheng