Measure wrongly summing column

[stefani_vileva]

Hello, I have three measures for summing up quantities, the first Prev. week is suming the quantity of the previous week, the second PrePrev. week is summing the quantity of the previous previous week and the last one Prev. week year is summing the quantity of the same week but previous year. 

How it is possible to get some value in the total section but there is no value in the column?

Thank you in advance.

Best regards,

Stefani

[stefani_vileva · Accepted solution]

I solved the problem. It was something wrong with the calculation of the week number, so instead of the formula above, I used the following code:

Previous year order (Kolli) = 

VAR WEEK_NUM = WEEKNUM(TODAY(), 2) - 1
VAR YEAR_WEEK = (YEAR(TODAY())-1)*100+WEEK_NUM
VAR YEAR_WEEK_RANK = CALCULATE(MAX('Date'[Week rank]), FILTER(All('Date'), 'Date'[Year week]=YEAR_WEEK))

VAR QUANTITY_PREVIOUS_YEAR_WEEK = CALCULATE(SUM(Lieferungs[KOLLI]), FILTER(ALL('Date'), 'Date'[Week rank]=YEAR_WEEK_RANK))

RETURN
    QUANTITY_PREVIOUS_YEAR_WEEK

[mlsx4]

I'm not sure what's happening with your measure, but it seems to be a problem of the formula: are you using SUMX for each row?

You should have something like PreYear = SUMX(VALUES(Table[EAN]), [Prev. week year])

[stefani_vileva]

Hello @mlsx4,

I am not using SUMX, instead I am using SUM, the problem is that inside I am trying to calculate the same value for the previous week in the previous year, for example let say this year we are in 26 week, so I want to calculate what we had in 25 week in 2022.

The formulas that I am using is following:

Previous year order (Kolli) = 
VAR CURRENT_WEEK = MAXX(ALL('Date'), 'Date'[Week rank])-1
VAR PREVIOUS_WEEK = CURRENT_WEEK-1
VAR WEEK_NUM = CALCULATE(MAX('Date'[Week number]), FILTER('Date', 'Date'[Week rank]=PREVIOUS_WEEK))
VAR YEAR_WEEK = (YEAR(TODAY())-1)*100+WEEK_NUM
VAR YEAR_WEEK_RANK = CALCULATE(MAX('Date'[Week rank]), FILTER('Date', 'Date'[Year week]=YEAR_WEEK))

VAR QUANTITY_PREVIOUS_YEAR_WEEK = CALCULATE(SUM(Lieferungs[KOLLI]), FILTER(ALL('Date'), 'Date'[Week rank]=YEAR_WEEK_RANK))

RETURN
    QUANTITY_PREVIOUS_YEAR_WEEK

 With this formula, I am only getting value for the first row but not for all the others that should be also data.