topic Re: Cumulative upto in Dax in DAX Commands and Tips

Cumulative upto in Dax

Anonymous — Sat, 07 Sep 2019 16:58:55 GMT

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.

Re: Cumulative upto in Dax

Greg_Deckler — Sat, 07 Sep 2019 18:10:45 GMT

Right, you want to use EARLIER like this:

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

Re: Cumulative upto in Dax

Anonymous — Sat, 07 Sep 2019 18:45:57 GMT

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

Re: Cumulative upto in Dax

Anonymous — Sat, 07 Sep 2019 19:19:41 GMT

Dear Anonymous

Kindly explain how does it work?

Thanks