Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi,
I want to create a table in a report with the first column's rows to show year or Month or dates and then to compare the value of sales of this year and last year I want to show the sale values of those periods in two different columns(Sales column and Sales Past Year column) like below, but For the current year I want it to calculate the Sales Past year considering days from the begining of the period mentioned until the equal date of last year. This means that If we are in the 2nd of Jan this year, I only want to see the sales of 1st and 2nd of Jan from last year in the Sales Past year:
Period | Sales | Sales Past year | Comparison |
2019 | 1500 | ||
2020 | 1800 | 1500 | 300 |
2021 | 1850 | 1800 | 50 |
2022 | 1750 | 1850 | -100 |
2023 | 300 | 200 | 100 |
About the period column, I want to be able to use Quarter, Month, Week,... For the first try I used this code:
@sarkani , if you have date , you can create date table with year , month/period etc. Join with your table on date.
and use date/period from date table in visual/slicer and measure, you can measure prior year like
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))
Take a diff
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
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
Hi and thanks for the reply,
I have a date table and I am using it but for some reason when I use the sameperiodlastyear function it brings the whole month for the same month I am from last year. I mean now that we are on 22nd May 2023, for 2022 it brings the whole May not until 22nd of it. One thing I noticed is that some of the sales have dates which are in future... Maybe that is why the function does not work. What should I do about that?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!