How can I calculate sales amounts for previous years but consider todays MMDD as max of last years?

[sarkani]

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:

Sales Sales Past year = SWITCH(TRUE(),

                    SELECTEDVALUE('Time'[Year])=YEAR(TODAY()), CALCULATE([Sales],'Time'[Date]<=date(YEAR(today())-1,MONTH(TODAY()),DAY(TODAY())) && 'Time'[Date]>= date(YEAR(today())-1,1,1)),

                    SELECTEDVALUE('Time'[Fiscal Year (N)])<YEAR(TODAY()),CALCULATE([Sales],SAMEPERIODLASTYEAR('Time'[Date])))

This works great for years, But when I change the period to other types like Months, It brings the same numbers for each month of this year.

I need to solve that and also I don't know If the code is the best possible or not. Can you help me with this?

[amitchandak]

@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

[sarkani]

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?