Forum Discussion

stefani_vileva's avatar
stefani_vileva
Resolver II
3 years ago
Solved

Measure wrongly summing column

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 wee...
Need Help
  • stefani_vileva's avatar
    stefani_vileva
    3 years ago

    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's avatar
mlsx4
Memorable Member
3 years ago

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's avatar
stefani_vileva
Resolver II
3 years ago

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.