topic YTD by week with no numerical order in DAX Commands and Tips

YTD by week with no numerical order

AgustinLopez — Thu, 11 Feb 2021 15:41:03 GMT

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!

Re: YTD by week with no numerical order

amitchandak — Thu, 11 Feb 2021 17:26:42 GMT

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])))

Re: YTD by week with no numerical order

AgustinLopez — Thu, 11 Feb 2021 18:19:52 GMT

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!

Re: YTD by week with no numerical order

amitchandak — Fri, 12 Feb 2021 03:43:27 GMT

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])