cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aaronzheng
Helper I
Helper I

Running total sum with a current hour flag column

aaronzheng_0-1634063284499.png

Hello I have a table called FLA with applications per state with time and date columns. There is also a current_hour_flag column that shows 1 if the time is the current hour. For instance, the last row has a current hour flag value of 1 because 10:00 am was the current hour. 

This is my runningtotal measure that sums up all of the applicationcount from the previous day to each hour. 

RT2 = CALCULATE(
    SUM('FLA'[ApplicationCount])
    ,FILTER(
            ALLSELECTED(FLA),
             FLA[Time] <= MAX(FLA[Time])
              && FLA[Date] = MAX(FLA[Date])
          )
     )

 
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) 

aaronzheng_1-1634063652419.png

141 rather than intended 768.

Thanks in advance.

1 ACCEPTED SOLUTION
TheoC
Memorable Member
Memorable Member

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

 

TheoC_0-1634588416616.png

 

 

TheoC_1-1634588416618.png

 

 

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

View solution in original post

20 REPLIES 20
TheoC
Memorable Member
Memorable Member

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

 

TheoC_0-1634588416616.png

 

 

TheoC_1-1634588416618.png

 

 

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

v-angzheng-msft
Community Support
Community Support

Hi, @aaronzheng 

 



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) 

aaronzheng_1-1634063652419.png

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

 

vangzhengmsft_0-1634636682153.pngvangzhengmsft_1-1634636695478.png

 

vangzhengmsft_2-1634636702061.png

 

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.

Hello, you were right that my measure was correct. Theo's current hour column did the trick and solved my issue. 

@v-angzheng-msft, if you read some of the other posts, the solution requires only the amount at the current hour to be shown. I have prepared the following which fits the requirements:

_______

 

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

 

TheoC_0-1634588416616.png

 

 

TheoC_1-1634588416618.png

 

 

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

TheoC
Memorable Member
Memorable Member

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

 

TheoC_0-1634588416616.png

 

 

TheoC_1-1634588416618.png

 

 

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

View solution in original post

Hello Theo, your calculated hour flag column solved my issue and I was able to use my previous measure. However, would you know how to adjust it so I could have a time slicer that would change the current hour to my selection?

aaronzheng_0-1634826211441.png

In other words, show the application count sum for the past 30 days at the hour I select.





TheoC
Memorable Member
Memorable Member

@aaronzheng great to hear it worked, mate!  It was probably one of the more complex ones I've got to work on here in the Community! It is always fun to learn new things, so thank you for posting!

 

I am not aware of a way to link the two due to the Current Hour Flag dictates the way in which the measure works to return them (i.e. it's progressive according to the current hour).

 

However, I believe there may be a way whereby you can create a dynamic measure based on a Slicers selected values.  So, the way that you would want to establish the measure with with logic that can apply the following:

 

Dynamic Measure = IF a user has selected an option from the Hour Slicer then present [Current Hour Measure] else present [Hour Measure]...

 

Actually, I just found this post here which is very closely aligned with the above lol: https://community.powerbi.com/t5/Desktop/Dynamic-Measure-Values-Based-on-Slicer-Selection-with-Optio...

 

Anyway, if you're happy with the solution relative to this post, make sure to mark it as solved and many thanks for posting your topic. It's been fun!

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

TheoC
Memorable Member
Memorable Member

@aaronzheng 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 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Hi Theo, I don't understand how to implement your method. I created the current_day_flag column, but how would I use it to adjust my dax measure. 

 

Heres a link to my sample data (didn't allow me to attach an excel file), the current hour at this time was 9:00 am. 

 

https://docs.google.com/spreadsheets/d/1Ddmx0vcP7t4J1W3l4YgnxYAlXK7ZtRDJ/edit?usp=sharing&ouid=10423...

TheoC
Memorable Member
Memorable Member

Hi @aaronzheng 

 

Okay, so finally got to this. Apologies for the delay and it required a little more effort than I thought... The "m1" measure is representative of yours and the "mea_Cumulative2" is the new measure.  I've tried to keep the names of the columns as aligned as possible. 

 

mea_Cumulative2 = 

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

 

Hope this is what you wanted! 🙂

 

TheoC_0-1634260895139.png

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Hey Theo, I really appreciate the effort you put into this. However, this is not exactly what I wanted. I forgot to mention that there is a current hour flag slicer that affects the visualization. When the slicer is set to 1, the line graph only plots the application count at the current hour (in this case 9:00 am ) for the last 30 days. In other words, it shows the application count at 9:00 am for the past 30 days. 

 

aaronzheng_0-1634308115944.png

 

In this screenshot it is filtered on CA (state) and shows the application count at the current hour (9:00 am). I am working on adjusting your mea_Cumulative2 measure to incorporate the current hour because currently it doesn't work with the current_hour_flag slicer. (It doesn't appear on the visual)

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:

aaronzheng_0-1634308553191.png

 




Thanks, Aaron.

 

 



TheoC
Memorable Member
Memorable Member

Hi @aaronzheng, just to confirm, all you are wanting is to return the max of my measure at the Current Hour Flag? If that is correct, you should be able to add a second measure that encompasses my existing measure to return the max value when Current Hour Flag is equal to the current hour of the ApplicationHourEST.

 

Give it a go, I won't be in front of computer until Monday. But make sure to let me know how it goes.

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

I don't believe your measure gives the correct sum, but I'll give it a go.

TheoC
Memorable Member
Memorable Member

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

 

TheoC_2-1634506094315.png

 

TheoC_3-1634506114253.png

 

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

TheoC
Memorable Member
Memorable Member

@aaronzheng is the above what you were wanting?

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

TheoC
Memorable Member
Memorable Member

Hi @aaronzheng,

 

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

 

Thanks,
Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Hi Theo,

 

The Y axis is my measure RT2 (shown above) and the X axis is ApplicationHourEST which is a date time.

TheoC
Memorable Member
Memorable Member

@aaronzheng, thanks for that.  Can you add a Calculated Column that gives you a Current Day Flag (i.e. replicating your Current Hour Flag column but for the respective date).  That should provide you with a value of 1 for the respective day and align to the Hours for the day commencing at 00:00.  From there, modify your measure to SUM the values of the ApplicationCount using the Current Day Flag that equal 1 rather than the Current Hour Flag that is 1. 

 

The Current Date Flag should be something as simple as "Current_Day_Flag = IF ( Table[ApplicationHourEST] = TODAY ( ) , 1 , 0 )".

 

Hope that makes sense?

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Thanks Theo, I'll give this a try. Would this work for multiple days? My visualization is suppose to show the sum of the applications for 30 days at the current hour. For example, in the screenshot in my original post, it shows the application count for the last 30 days at 10:00 am each day.

TheoC
Memorable Member
Memorable Member

@aaronzheng just saw your messages and your sample data. Will get back to you today AEST time 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.