Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
StuartSmith
Power Participant
Power Participant

Previous YearS CALCULATE(SUM( Formatting

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
1 ACCEPTED SOLUTION
StuartSmith
Power Participant
Power Participant

 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

View solution in original post

4 REPLIES 4
StuartSmith
Power Participant
Power Participant

 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
Super User
Super User

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.