Forum Discussion

1up's avatar
1up
Icon for Resolver I rankResolver I
3 years ago
Solved

Measure for summing sales where the condition differ depending on the year

Hi,   I need to sum up sales for a 2-year period. The thing is that the first year only needs to be based on actual sales. While the second year needs to include both actual and forecast sales, lik...
Show and Tell
  • amitchandak's avatar
    amitchandak
    3 years ago

    1up , Try like

    calculate(Sumx(filter('SalesType', 'SalesType'[SalesType] = "Delivered"), [Sales]), filter('Year' , 'Year'[Year]<= 2023)) +
    calculate(Sumx(filter('SalesType', 'SalesType'[SalesType] = "Delivered"), [Sales] ) + [Forecast], filter('Year' , 'Year'[Year] = 2024))

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

1up , Try like

calculate(Sumx(filter('SalesType', 'SalesType'[SalesType] = "Delivered"), [Sales]), filter('Year' , 'Year'[Year]<= 2023)) +
calculate(Sumx(filter('SalesType', 'SalesType'[SalesType] = "Delivered"), [Sales] ) + [Forecast], filter('Year' , 'Year'[Year] = 2024))

  • 1up's avatar
    1up
    Icon for Resolver I rankResolver I
    3 years ago

    Thank you very much amitchandak. With a small adjustment I got the formula to work. I added an or-condition for Forecast sales, like this;

    Combined sales = calculate(Sumx(filter('SalesType', 'SalesType'[SalesType] = "Delivered"), [Sales]), filter('Year' , 'Year'[Year]<= 2023)) +
    calculate(Sumx(filter('SalesType', 'SalesType'[SalesType] = "Delivered"), [Sales] ) || 'SalesType'[SalesType] = "Forecast", filter('Year' , 'Year'[Year] = 2024))