Forum Discussion
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.
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
- Greg_Deckler
Community Champion
Right, you want to use EARLIER like this:
Column = VAR __table = FILTER(ALL('Table2'),[Car] = EARLIER([Car]) && [Sequence] <= EARLIER([Sequence])) RETURN SUMX(__table,[Hours])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.
