cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

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

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?

2 REPLIES 2
Super User

@Anonymous , 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

Anonymous
Not applicable

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?