Previous YearS CALCULATE(SUM( Formatting

[StuartSmith]

I am trying to display in a card the previous years total sales value, so as we are in 2022, I want to display 2021, and then next year I want it to display 2022 total sales.

In testing I have the below and this gives me the correct figures...

Total_Previous_Year_Spend =
CALCULATE(SUM('MDSL Output'[Net Amount]), Dates[Year]="2021")

but need something like the below so every new year it will automatically look at the previous year...

Total_Previous_Year_Spend =

CALCULATE(SUM('MDSL Output'[Net Amount]), Dates[Year]=YEAR(TODAY())-1)

But it says it cant display the visual (card).  What am i doing wrong?

Thanks in advance

[StuartSmith · Accepted solution]

 OK, Figures it out.  Might not be the most elegant code, but it works...

Test_Test_Test_Total_Previous_Year_Spend =

Var Previous_Year = YEAR(TODAY())-1

Var Previous_Year_to_Text = CONVERT(Previous_Year,STRING)

Var Total_Previous_Year_Spend = CALCULATE(SUM('MDSL Output'[Net Amount]), Dates[Year]=Previous_Year_to_Text)

Return

Total_Previous_Year_Spend

[amitchandak]

@StuartSmith , example with help from date table and time intelligence

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

//Only year vs Year, not a level below

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

[StuartSmith]

Thanks and will take a look at your solutions shortly, but curious as to why the Var Total_Previous_Year_Spend2 doesnt work using the "Previous_Year" variable.

Test_Test_Test_Total_Previous_Year_Spend =

Var Previous_Year = YEAR(TODAY())-1 // This works 🙂

Var Total_Previous_Year_Spend1 = CALCULATE(SUM('MDSL Output'[Net Amount]), Dates[Year]="2021") // This works 🙂

Var Total_Previous_Year_Spend2 = CALCULATE(SUM('MDSL Output'[Net Amount]), Dates[Year]=Previous_Year) // Why does this not work? 😞

Return

Total_Previous_Year_Spend1

[StuartSmith]

Would i need to convert the "Previous_Year" number into a string, so it works?

Var Total_Previous_Year_Spend2 = CALCULATE(SUM('MDSL Output'[Net Amount]), Dates[Year]=Previous_Year)