Forum Discussion
Helper ITotal Sales Last Year
Hello All,
I have a question that I have seen on here multiple times, however I am having an issue that I have not seen. I have created a measure to display 'Last Years Sales' using the following formula: Total Sales LY = CALCULATE(SUM('IHeads'[Sales]),SAMEPERIODLASTYEAR('Calendar'[Date]))
Below is the output, as I do have values returned:
I am having trouble as the measure I created is giving me the same value as my Total Sales measure
Total Sales = SUM(IHeads[Sales])
Can anyone assist, and advise why the measures are calculating th same values? Hopefully, it is a small piece that I am misunderstanding.
Thank you!!
7 Replies
SeanCommunity Champion
umpoohgIt looks like you have a Calendar Table and you are using the Year field from it in the Table Visual
These Measures should work...
Total = SUM ( 'IHead'[Sales] )
PY Total = CALCULATE ( SUM ( 'IHeads'[Sales] ), PREVIOUSYEAR ( 'Calendar'[Date] ) ) YoY % Change = DIVIDE ( ( [Total] - [PY Total] ), [PY Total], 0 )Hope this helps.
Good Luck! :smileyhappy:
- umpoohg
Thank you so much for your reply!! I have my measures as so:
Total Sales = SUM(IHeads[Sales])
Total Sales LY = CALCULATE(SUM('IHeads'[Sales]), PREVIOUSYEAR('Calendar'[Date]))
% Chg vs LY = DIVIDE([Total Sales]-[Total Sales LY],[Total Sales LY])
The only one that is not working is the Total Sales Last Year
I have also tried:
Total Sales LY = CALCULATE([Total Sales], PREVIOUSYEAR('Calendar'[Date]))
Total Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date])) - gives me contiguous date error
david40niI am facing the exact same problem its returning the same figures as the current year did you ever solve this at all?
- Datatouille
Solution Sage
Hi umpoohg
The syntax of your measure is correct but:
1. Did you make sure you have created a relationship between Calendar and Sales Table ?
2. What is the current filter context regarding Calendar ? Do you use a slicer or a 'row'/'column' argument from the calendar table to filter the data ?
- umpoohg
Yes, I thought of that right after I sent the original post. Now that I am thinking, maybe it is still a problem with this relationship
Hi ,I use below calculation to return last year to date, ubt it returned last year plus this year to date. Any idea?
I have a calendar table like below , and the year to date calculates fine.
Total Sales YTD = TOTALYTD([Total Sales],'Calendar'[Date])
Total Sales YTD LY = calculate([Total Sales YTD],dateadd('Calendar'[Date],-1,YEAR))
Try using a ' CurrentYearOffset' for your measure to work.
I'm pretty sure, if you set this on your visual or your page, the measure will start working.
