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

[1up]

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, like this:

Year 2023 - should use Actual Sales

Year 2024 - should use Actual Sales + Forecast Sales

Sales exist as a measure.

Related table SalesType exist with one column, SalesType[SalesType]

including fields: 'Actual Sales' and 'Forecast Sales'.

Both exist with sales for year 2023 & 2024.

Related table Year exist as table Year with one column Year, 'Year'[Year]

The wanted end result is that the measure does the correct summing, when adding column 'Year', like this:

Example values

Year     Sales    (Based on)

2023    100       Actual Sales

2024    150       Actual Sales (90) + Forecast Sales (60)

This example is simplified, I need to do it for more than two years, and using additional sales types.

I have come so far as to summing the sales using calculate and sumx functions. I get stuck when using if-statement:

Measure = If(FILTER(RELATEDTABLE('Year'),'Year'[Year] = 2023)

                     Calculate(Sumx(' Measures',[Sales]),'SalesType'[SalesType] = "Delivered")...

(The measure continue with one if statement for each year)

I get error: 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.'

Is it understandable?

Thanks for any ideas,

Daniel

[amitchandak · Accepted solution]

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

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