Difference between SUM and value of differents models

[ESS]

Hello,
I need help to plan a check between two numbers on different models.
I have one Sharepoint extraction with this data:

Year	Week	Data
2021	1	25
2021	1	45
2021	3	70
2020	5	18
2020	5	15

Where by using a slicer I check Years/Week Data SUM.

I also have another Sharepoint data list like this:

Year	Week	Data
2021	1	200
2021	2	150
2020	3	300
2021	4	270
2020	5	250

Is there a way to connect 2 models and have the difference between the SUM of the "Data" of the first Sharepoint list and the TOTAL of the single line by Year/Week on the second Sharepoint list?

Like:

200 - 25+45 (of the week 1 on year 2021) = 130

Thank you!

[v-angzheng-msft · Accepted solution]

Hi, @ESS 

To create  Year-Week calculated column in two tables:

Year-Week T1 = COMBINEVALUES("-",'T1'[Year],[Week])

Year-Week T2 = COMBINEVALUES("-",'T2'[Year],[Week])

Then to create a measure like this:

_diff =
VAR _sum =
    CALCULATE (
        SUM ( 'T1'[Data] ),
        FILTER ( 'T1', 'T1'[Year-Week T1] = MAX ( 'T2'[Year-Week T2] ) )
    )
RETURN
    SUM ( 'T2'[Data] ) - _sum

result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ESS]

Thank you so much 🙂

[amitchandak]

@ESS , Create a common table for year week nad join with both of them

New table= distinct(union(summarize(Table1, Table1[Year],Table1[week]),summarize(Table2, Table2[Year],Table2[week])))

Create a new column in all three tables 

year week = [Year]*100 + [Week]

Join the new table with the other two and analyze it together 

new measure = sum(Table1[data]) -sum(Table2[Date])

[ESS]

Thanks for the answer.

I didn't understand how it work. Distinct formula remove duplicates but on there is Week 30 in 2020 and also in 2021. I'm sorry but I wrote just 2021 in my example but I have few years on the table.