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

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

Reply
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:

 

PeriodSalesSales Past yearComparison
20191500  
202018001500300
20211850180050
202217501850-100
2023300200100

 

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
amitchandak
Super User
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
https://www.youtube.com/watch?v=km41KfM_0uA

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?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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