Forum Discussion

atult's avatar
atult
Icon for Advocate I rankAdvocate I
4 years ago
Solved

Last 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...
Need Help
  • MahyarTF's avatar
    MahyarTF
    4 years ago

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

     

MahyarTF's avatar
MahyarTF
Icon for Memorable Member rankMemorable Member
4 years ago

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's avatar
    atult
    Icon for Advocate I rankAdvocate I
    4 years ago

    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's avatar
      MahyarTF
      Icon for Memorable Member rankMemorable Member
      4 years ago

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