DAX to collect sum from 2 or more columns

[KlasDK]

I need a DAX formula that I can use for the following.

In one of my sheets, change per item no and with week number will be collected.
In another sheet I have sales per week per item no.
Match the rows and weeks 1-52 in the columns

If a change of item takes place in week 39, then the DAX formula must collect a change from week column 40-52. And if the change takes place in week 20, it will collect for 21 - 52

Is there anyone who has a good solution to this challenge?

[KlasDK]

This one over here is the sales data.   If there in the box down under whas standing 4042 dddd will be change in WEEEK 3 it shold take all the data from WEEK 4 WEEK 5 ....... WEEK 52 in the line of 4042 Thats is the total sale for the periode beging ind the WEEK after the number in the box down under. 

The sales Data sheet will always have 52 WEEK The weeks that have not been yet will contain 0

[v-yulgu-msft]

Hi @KlasDK,

Below is the test based on my assumption. If I have something misunderstood, please post an image to show your desired output.

First, please unpivot the sales data table.

In data view, create some calculated columns.

Change week No =
LOOKUPVALUE (
    'Change Week'[Change Week No],
    'Change Week'[Vare No], 'Sales Data'[Vare]
)

Total =
IF (
    VALUE ( RIGHT ( 'Sales Data'[WeekName], 1 ) ) <= 'Sales Data'[Change week No],
    'Sales Data'[Value],
    CALCULATE (
        SUM ( 'Sales Data'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Sales Data', 'Sales Data'[Vare] ),
            VALUE ( RIGHT ( 'Sales Data'[WeekName], 1 ) ) > 'Sales Data'[Change week No]
        )
    )
)

Insert a Matrix visual, drag relative fields from above table.

Best regards,
Yuliana Gu

[KlasDK]

Hi 

Now i have been looking and think and it not that right solutions for me. But it is at god step of the way.  Especially the thing whith unpivot the tabel. 

but the result I need is the DAX to Cocet the total sum af sales on a "Vare" from the weeek in the celle calede "Ændrings uge CBP" and then the rest of the Year.  So if the week number in "ændrings uge CBP" is 20 ist shall take the sales amount from 20 + 21 + 22..... + 52  (the sales before week 20  may not be in the calculation). And that calculation have to be in at calculatede colum so i can make som more calculations whith it. 

The shets whit "ændrings uge CBP" in can have the same vare servale times whith difference week in it. Thats why i can se it is not god to set the number in the sales shets. 

I hope the above makes sense.

[KlasDK · Accepted solution]

The solutions i fund and working for me.

=CALCULATE(sum(Side1_1[Værdi]); filter(Side1_1; Side1_1[Uge nr.]> 'PÆ skema'[Ændringsuge  CBP] && Side1_1[Vare nr] = 'PÆ skema'[Vare nr.]))

[Omega]

Can you please share some screenshots or the PBIX you are trying to create?