Running total sum with a current hour flag column

Hi Anonymous,

Can you please share the details of your Y and X Axis on the line graph? 

Thanks,
Theo

Anonymous yes, it should work as it's running a very similar logic to your current approach. 

If it doesn't work, please share a data set. You can right click the Table in tabular view and press Copy Table and paste in Excel, de-identify, and upload that if it doesn't work 🙂

Hi, Anonymous 

---

My issue is that with the current hour flag column, it only returns the application count at that hour, rather than the sum of the day's application counts up to that hour. For instance, the measure returns 141 rather than 768 (Sum from 12:00 am to 10:00 am, please refer to first screenshot) 

141 rather than intended 768.

---

I don’t think you need to make any changes to your measures.

If I understand your requirements correctly, you need to calculate the running total of the application count of the day until a certain hour.
Your measures are exactly what you want. I did not make any changes to your measure, and I got the results that fully meet your needs.

The screenshot above also shows 49 as the application count for CA on 10/9/2021 at 9:00 am, but I want it to show 510 as the sum of the application counts on 10/9/2021 until 9:00 am. 510 is the sum of the application counts (3rd column) shown below in this screenshot:

1)--->768

2)--->510

Maybe you made a mistake in some operations, but your measure is completely correct.

Please refer to the attachment below for details.  Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @aaronzheng 

I tested my theory this morning and, if I understand what you are wanting which is to show all of the Current Hour Flags for the past n number of days, all you need to do is create a Calculated Column that says to return the current date and time if it equals the Current Hour Flag. 

col_Current_Hour_Flag = IF ( Table1[Current_Hour_Flag] = 1 , Table1[ApplicationHourEST] , BLANK() )

The output gives you the Current Hour and, when added to the visual with the measure I created in the earlier post being mea_Cumulative_Day, it gives you the total cumulative at the current hour for the "Current Hour Flag".  The new column uses the ApplicationHourEST and only presents a Date/Time if the Current Hour Flag is true and you use this new column as your X-Axis. Important, ensure you convert the output to Data/Type = Date/Time and then when you drag it in as the X-Axis on the visual, change it from Date Hierarchy to standard Date/Time.

I did notice one thing in my previous mea_Cumulative_Day measure in that it didn't add the ApplicationCount between 00:00 to 01:00 or the ApplicationCount in the 09:00 parameter. The reason for the 09:00 is because it includes all counts > 09:00 and < 10:00.  But, to get the 503 count, I have modified my earlier measure to the below:

mea_Cumulative_Day = 

VAR _CurTime = MAX ( 'Table1'[ApplicationHourEST] )

RETURN

    IF ( 
        TIMEVALUE ( _CurTime ) <> TIME ( 0 , 0 , 0 ) ,
            CALCULATE (
                SUM ( Table1[ApplicationCount] ),
                FILTER (
                    ALLSELECTED ( Table1 ),
                    AND ( Table1[ApplicationHourEST] >= DATEVALUE ( _CurTime ) , 'Table1'[ApplicationHourEST] <= _CurTime )
                )
            ) , 
            CALCULATE (
                SUM ( Table1[ApplicationCount] ) + 0 ,
                FILTER (
                    ALLSELECTED ( Table1 ) ,
                    DATEVALUE ( Table1[ApplicationHourEST] ) = DATEVALUE ( _CurTime ) - 1 )
                )
            )

In the event that your "Current Hour Flag" is a measure, just convert the Calculated Column to a Measure.  In my data, I have set up the Current Hour Flag as a column, therefore, I've used this as the reason for creating a Calculated Column in this below example. 

Please accept this as a solution if it is what you are after.

Thanks,

Theo