Cumulative total per date and hour

[Ghaston]

Hi guys 

HEEELP !
I need to calculate the cumulative total for two mesures ( CBM.61 and Entr.Arch.61).
The problem is that i need to run the cumulative total per date and time ( hours and minutes). 
Because i get the data from an SQL server that updates my Dashboard every 5 minutes. 

I need then to build a line diagram where the X axes should be " date and time ".
Here is the link to the Power BI file. 
https://drive.google.com/open?id=16Qo5XyNMd1zdvKgjcXoPKGzxVO3varxf
You'll find in "page 2" this table : 
It contains the two mesures for witch i need to run the cumulative total, per date and time (hours and minutes). 
And i need to add time (hours and minutes) to this table, and sort it by date and time.

I'am counting on your help guys ! 

 

[Greg_Deckler]

Have not had time to sit down with the PBIX but in general when you need running totals of measures, the process is essentially to:

1. Create one or more VAR statements that get the MAX of your date/time values like month or hour. In the context of a visual, this will grab the current hour or month, etc.
2. Create a VAR that does a SUMMARIZE of ALL of your information and either include your measures in that SUMMARIZE or use ADDCOLUMNS to add those measures. This removes the context from the visual because you want a running total versus just exactly what the visual filters the data to like a specific month or hour
3. Use your previously created VAR statements to filter your table created with your SUMMARIZE statement to FILTER the information to all rows less than the current hour/month/etc.
4. Use SUMX or other aggregation to sum up your measure columns from your FILTER'ed SUMMARIZE table.
5. Return that value.

[Ghaston]

Hi @Greg_Deckler

Well, i run cumulative totals before, but i've done it per day, or month. 
But i couldn't do it while taking time into account. I just don't know how to deal with time. 
Even when i create a table with the date/time column, only the date is displayed. However the type of the column is ( date/time). 

[Greg_Deckler · Accepted solution]

Standard cumulative total methods should work with date/time in addition to just date. Date and date/time values are really just decimal numbers where the integer portion is the number of days since December 30th, 1899 and the decimal component is the time component. So if you FILTER your table by date/times less than your MAX date/time (in context of a visual) then it should return everything that is earlier including the time components. So if you have a date/time of 8/23/2018 1:00:00 PM then everything prior to that date and time like 8/23/2018 12:59:59 PM should be prior to that date/time and something like 8/23/2018 1:01:00 is after that date/time.

I'm not sure what you are talking about with regard to putting a column like that into a table visualization and not seeing the time component. Just use the drop down arrow on the column in the Values area and switch from Date Hierarchy to the name of your column. Then your time component will be displayed.

[Ghaston]

Thanks for telling me that total methods works with date/time, so i had just to try to fix my formula. 
And it works perfectly now 🙂
 Thanks for help ! 
i appreciate it