Forum Discussion
Update Year in Sales Statistic
Hi All
We are using several Power BI statistics to have an overview of our current sales data. We are filtering these data for the current year, previous year etc.
At the moment we are filtering that data manually by typing in 2021, 2020 etc. However, this is a lot of effort to update this every year and leads to frustration when the statistics are not up to date. Is there a way to update this automatically so that the current year is always the current year?
I have tried to do this with the date filters and with the report filters but with no luck:
Current Filter in Reports:
Anonymous , You can use this year last year with time intelligence with Date table and default year filter
Meausre
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"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uADefault year - create a column in date table and save in slicer : https://www.youtube.com/watch?v=hfn05preQYA
Year Type = Switch( True(),
year([Date])= year(Today()),"This Year" ,
Format([Date],"YYYY")
)
3 Replies
- amitchandak
Super User
Anonymous , You can use this year last year with time intelligence with Date table and default year filter
Meausre
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"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uADefault year - create a column in date table and save in slicer : https://www.youtube.com/watch?v=hfn05preQYA
Year Type = Switch( True(),
year([Date])= year(Today()),"This Year" ,
Format([Date],"YYYY")
)Hi amitchandak
After implementing and testing of the data. I have noticed that I get the same result for Last YTD Sales and Last Year Sales.
Obviously there is something wrong. I have double checked the data directly in SQL and can confirm that Last Year Sales is correct. There must be an error in the Last YTD sales.I only want to see the data from 01.01.2020 to 26.02.2020 and not for the entire 2020.
Any Idea what I am doing wrong?I am using the following dax:
Turnover last year(YTD) = CALCULATE(SUM(BelegLines[AmoutNoVat]),DATESYTD(dateadd(BelegLines[Shipment Date].[Date],-1,Year),"12/31"))