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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JonasDedual
Helper I
Helper I

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

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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

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

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.