Forum Discussion
atult
Advocate I
Advocate ILast Year Same Period Sales
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 f...
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 settingvar _fromYear=2021 // set the start year of the date dimension. dates start from 1st of January of this yearvar _toYear=2022 // set the end year of the date dimension. dates end at 31st of December of this yearvar _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()returnADDCOLUMNS(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 :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 :
atultAdvocate I
Advocate IHi 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
Memorable Member
Memorable MemberHi,
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 :
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 :