topic Re: Sum of Sales at a given point in time in DAX Commands and Tips

Sum of Sales at a given point in time

Euro0681 — Tue, 03 Jan 2023 16:58:53 GMT

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

Re: Sum of Sales at a given point in time

amitchandak — Tue, 03 Jan 2023 17:06:20 GMT

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

Re: Sum of Sales at a given point in time

Euro0681 — Tue, 03 Jan 2023 17:09:15 GMT

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

Re: Sum of Sales at a given point in time

Euro0681 — Tue, 03 Jan 2023 17:12:00 GMT

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?

Re: Sum of Sales at a given point in time

tamerj1 — Tue, 03 Jan 2023 18:49:24 GMT

Hi Euro0681

please try

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

Re: Sum of Sales at a given point in time

Anonymous — Tue, 31 Jan 2023 09:24:44 GMT

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.