cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
sarkani
Helper II
Helper II

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

@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?

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

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!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors