Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Ghaston
Helper I
Helper I

Cumulative total per date and hour

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. 
Capture.PNG
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.
Capture.PNG
I'am counting on your help guys ! 

 

1 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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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.

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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). 
Capture.PNGCapture.PNG



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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.