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
N90r
New Member

Cumulative upto in Dax

Hello,

I hope my query doesn't sound funny but I am trying to create a progress chart for the car usage per sequence ( trip ).

Here is an example of how the table looks like and the last column is the result I am trying to generate using Dax.

Sequence Car type Hours/Sequence
1 Toyota 5
2 Toyota 9
3 Toyota 1
4 Toyota 3
1 Ford 19
2 Ford 7
3 Ford 4
4 Ford 6
1 GMC 10
2 GMC 1
3 GMC 16
4 GMC 5
1 Nissan 7
2 Nissan 0
3 Nissan 3
4 Nissan 1

And the additional generated column using DAX should look like this.

Cumulative Hours upto this sequence
5
14
15
18
19
26
30
36
10
11
27
32
7
7
10
11


Do you think its possible to create a Dax loop to distinguish between different car types and calculate the sum of hours up to each sequence.


3 REPLIES 3
Greg_Deckler
Super User
Super User

Right, you want to use EARLIER like this:

 

Column = 
VAR __table = FILTER(ALL('Table2'),[Car] = EARLIER([Car]) && [Sequence] <= EARLIER([Sequence]))
RETURN SUMX(__table,[Hours])

@ 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...
Anonymous
Not applicable

Hi @Greg_Deckler.

 

Could I please kindly ask you to stop using EARLIER, which is a rather hard-to-understand function and confusing for newcomers, and use the modern and easy way to address previous row contexts? The way is called VARiables. EARLIER is a remnant of the past and should rest in peace.

 

Many thanks.

 

Best

D.

Dear @Anonymous 

 

Kindly explain how does it work?

 

Thanks

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.

Top Solution Authors