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

 

 

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

3 Replies

  • stefani_vileva's avatar
    stefani_vileva
    Resolver II
    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.