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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rluongo
Frequent Visitor

Running total last 5 seconds data

Hi All,

my table is very simple 

TimeTime_5secBackValueRunningTotal
02:16:4102:16:366.595522 
02:16:4202:16:378.453462 
02:16:4302:16:386.175027 
02:16:4402:16:397.015015 
02:16:45 02:16:403.0925131.33154
02:16:4602:16:413.38133628.11735
02:16:4702:16:423.42302623.08691
......... 

 

I have to calculate the cumulative value of the last 5 seconds so basically

from sec 41 to 44 - 0

sec 45  - the sum of the previous 5 seconds - 31.33

sec 46 - the sum of the previous 5 seconds - 28.11

 

It is very easy in excel this sliding sum but I do not know how to replicate in in dax.

 

Any help appreciated

Roberto

 

 

3 REPLIES 3
Greg_Deckler
Super User
Super User

You should be able to create a column like:

 

Running Total =
  SUMX(FILTER('Table',[Time] >= [Time_5secBack] && [Time] <= EARLIER([Time])),[Value])

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks but I would like to work with a measure and EARLIER is not suggested

Just use a var, this should be the equivalent measure:

 

 

Running Total =
  VAR __Time = MAX('Table'[Time]) //max of time column in current context (measure)
  VAR __Time5s = MAX('Table'[Time_5secBack])
RETURN
  SUMX(FILTER(ALL('Table'),[Time] >= __Time5s && [Time] <= __Time),[Value])

 

 


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.