cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JonasDedual
Frequent Visitor

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:

 

JonasDedual_0-1613984510889.png

 

 

Current Filter in Reports:

JonasDedual_1-1613985334092.png

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@JonasDedual , 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_0uA

 

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
amitchandak
Super User IV
Super User IV

@JonasDedual , 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_0uA

 

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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

Thank you very much. That works for me!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors