sum values less than number selected from slicer

[rob_vander]

Hi All,

I have two tables Data & Sequence Dim. Both tables are linked on Sequence. In reality it's many to one join, but it's basic sample so you can see one to one relation.

Data table:

Sequence Dim table:

Requirement:

When I select any number from Sequence field in Sequence Dim table, I need to check that Sequence field in Data table and pick all the number less than or equal to selected number for each Type and apply sum over that Value.

for eg. When I select number 5 from Seqence in Sequence Dim table, I need to show below output

Type	Value	check <=5 Sequence in data table
a	45	1+2+4
b	95	3+4+5
c	215	2+3+4

When I select number 3 from Seqence in Sequence Dim table, I need to show below output

Type	Value	check <=3 Sequence in data table
a	20	1+2
b	40	3
c	125	2+3

Similarly, user can select any number and dynamically report should reflect the values based on selection

Note:  I don't want to break or disconnect relationship between the table.

[kushal_chawda · Accepted solution]

@rob_vander  If I understood it correctly you can try below DAX

sum of value = 
 VAR selected_sequence = SELECTEDVALUE('Sequence Dim'[Sequence])
 VAR sum_value = CALCULATE(SUM(Data[Value]),
                              REMOVEFILTERS('Sequence Dim'),
                             KEEPFILTERS(Data[Sequence]<= selected_sequence))
RETURN sum_value

[sagarp]

Hi Kushal,

I have Fiscal Year and Fiscal Week column, and If I select any fiscal week from slicer then it give me sum from First fiscal week to selected week on slicer. So, your trick worked for me. But I also want to get same data for last year. Can you help me with that?

For example, In slicer we select Fiscal Year 2024 Week 5, then based on your DAX code I got the sum of sales for Fiscal week 1 to Fiscal week 5. And I want to compare this data with sum of sales of Fiscal year 2023 from Week 1 to 5.

[rob_vander]

@kushal_chawda that works