Sum of Sales at a given point in time

[Euro0681]

Hey Everybody,
I'm wanting to calculate the sum of sales for a given year (Always the previous year), but I dont wan't this to be applied at a row by row basis.. For better clarification here's an example

So to clarify in this example I have filtered the date to show the previous 3 years of data, and I want to create a measure that calculates the sum of sales for 2021. The reason for this is because I want to look at the MAX(Year) we have and give me the previous year of that.

I want the measure to display like the table on the right, Not the table on the left. I have tried....

Measure = Calculate( Sum('Table 1'[Sales]) , 'Table 2'[Fiscal Year] = MAXX( ALL('Table 2') , 'Table 2'[Fiscal Year] ) - 1 )

which would work but an issue I'm facing is that the tables have a visual Filter Applied to show the Last 3 years of data but in my  Date Table I have a range of 2020 - 2027 for fiscal year so in this case my measure only gives me Sum of sales for 2026 (Which returns blanks) 
So I Then tried...  

Measure = Calculate( Sum('Table 1'[Sales]) , 'Table 2'[Fiscal Year] = MAXX( ALLSELECTED('Table 2') , 'Table 2'[Fiscal Year] ) - 1 )

But this gives me the table on the left in the image and that's not the correct results can anyone help me out or have any suggestions??

[Anonymous · Accepted solution]

Hi @Euro0681 

I created some data:

Table1：

Table2：

Here are the steps you can follow：

1.The two tables form the following relationship.

2.Create measure.

Measure =
SUMX(
   FILTER(ALL('Table1'),
   YEAR('Table1'[Date])=MAX('Table2'[Fiscal Year])),[Value])

Measure 2 =
SUMX(FILTER(ALL('Table2'),
'Table2'[Fiscal Year]=YEAR(TODAY())-1),[Measure])

The result is shown as below :

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @Euro0681 

please try

Measure2 =
CALCULATE (
SUM ( 'Table 1'[Sales] ),
'Table 2'[Fiscal Year]
= YEAR ( MAXX ( ALL ( 'Table 1' ), 'Table 1'[Date] ) ) - 1
)

[Euro0681]

And also this table is using columns from 2 tables from the model (Date Dimension & Sales Fact), where there be a reason to create another date table?

[amitchandak]

@Euro0681 , Not very clear. How come all are 700?

Create a separate date table with distinct year and join that with year of this table. Use year from new table in slicer and visual 

example measure for this year vs last year

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))

Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

[Euro0681]

All are 700 because the Maximum Year is 2022 so it'll minus 1 from 2022 and return sales for 2021 which are 300 + 400 return 700