YTD by week with no numerical order

[AgustinLopez]

Hello!
I need help creating the following Dax for YTD, I have this data

Week         Kg
5                10

6                7
7                9
8                11
9                12
1                 5
2                 3 
3                 7
4                 8

I want to creat a Sum that gives me the YTD of those kilograms, but the normal formula with <= max doesn't work due to how the weeks are displayed (has to be like that, because the season start in week 12 and ends at week 11)

Week         Kg       KG YTD
5                10         10

6                7           17
7                9           26
8                11         37
9                12         49
1                 5          54
2                 3          57
3                 7          64
4                 8          72

Total           72        72

Thanks!

[amitchandak]

@AgustinLopez , Solution 1 : Add an index column in power query and try  -https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

sumx(filter(allselected(Table), Table[Index] <=max(Table[index])), Table[Kg])

Solution 2

there needs to be a year column here. logic to have 1 after 9 ?

You need to have separate  Week/year table with year week and joining to this table year week

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

The cumm can be

CALCULATE(sum('order'[Qty]), FILTER(ALLselected('Date'),'Date'[Year]<=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] <= Max('Date'[Week])))

[AgustinLopez]

Mr Amitchandak:

The first solution works only for 1 year selection, but when I select more than 1 year in a slicer it combines both of them , see attached

how can I fix the dax so it work for all years separately so I can compare

Best regards!

[amitchandak]

@AgustinLopez , if the first one  is working and you have year too

sumx(filter(allselected(Table), Table[Index] <=max(Table[index]) && Table[Year] =max(Table[Year])), Table[Kg])