Last Year Same Period Sales

[atult]

Hi Experts,
I need to calculate the last year same period sales in a calculated measure.

Here, the 1 period = 4 weeks and hence there are 13 periods in an year.

Here is the table where Period format and Sales are given. Column 3(Required Measure) needs to be calculate:

Period	Sales	Required Measure
2021P01	100
2021P02	200
2021P03	300
2021P04	400
2021P05	500
2022P01	600	100
2022P02	700	200
2022P03	800	300
2022P04	900	400
2022P05	1000	500

Thanks in advance !

[MahyarTF]

Hi,

In your Date dim, add the below column :

"Day Period Num", trunc( DIVIDE((DATEDIFF(DATE( YEAR([Date]), 1, 1),[Date],DAY)+1),28,0))

Then Manage Relationship between your table and Date table and then use SAMEPERIODLASTYEAR ( <Dates> ) Dax code in your measure

[atult]

Hi @MahyarTF ,
Created calculated as you suggested and mapped to the main table but didn't work. The values were only returned for the last period and that too were quite weird values.

[MahyarTF · Accepted solution]

Hi,

1- create Dim Date with using the below script :

Date =

//************** Script developed by RADACAD - edition: July 2021

//************** set the variables below for your custom date table setting

var _fromYear=2021 // set the start year of the date dimension. dates start from 1st of January of this year

var _toYear=2022   // set the end year of the date dimension. dates end at 31st of December of this year

var _startOfFiscalYear=7 // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7

//**************

var _today=TODAY()

return

ADDCOLUMNS(

    CALENDAR(

                DATE(_fromYear,1,1),

                DATE(_toYear,12,31)

),

"Year",YEAR([Date]),

"Month",MONTH([Date]),

"Day",DAY([Date]),

"Day of Year",DATEDIFF(DATE( YEAR([Date]), 1, 1),[Date],DAY)+1,

"Day Period Num", format( trunc( DIVIDE((DATEDIFF(DATE( YEAR([Date]), 1, 1),[Date],DAY)+1),28,0)), YEAR([Date])&"P0#")

)

* you could copy the complete Date Dim from the below site :

https://radacad.com/all-in-one-script-to-create-calendar-table-or-date-dimension-using-dax-in-power-...

2- Then Create relationship between your table (it is named Sheet32 in my script) and Dim date :

3- In the main table, create Measure for calculated the last year sales amount :

LastYearSales = CALCULATE( sum(Sheet32[Sales]), SAMEPERIODLASTYEAR('Date'[Date]) )

4- Now you could use the particular measure in your visual :

[Anonymous]

@atult if you can have year column seperately,

then you can use this dax to get py sales.

Accept the solution if it works for you.

[atult]

Hi @Anonymous ,
It didn't work for me. Are you creating a calculated column or measure for this?

[Anonymous]

@atult it is a measure.