Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Comparing Periods when SAMEPERIODLASTYEAR doesn't work

Hello,

 

I am trying to compare sales data this fiscal period vs the sale fiscal period last year. Until now I have been using SAMEPERIODLASTYEAR but have now realized that it is not always accurate. This is why:

 

This fiscal year, a given fiscal period stretched from Oct 13 - Nov 9. It began on Sunday and ended on Saturday.

The previous fiscal year, the same fiscal period streched from Oct 14 - Nov 10. It is also aligned with four weeks Sunday-Saturday.

 

If we use SAMEPERIODLASTYEAR, we are comparing this year's fiscal period to the same calendar days last year, but it is off by a day on either side when comparing to the same period last year.

 

I did try the following formula, but it has limitations. For example, it will not show sum in a table. It also will not show us progress mid-period.

 

Sale KG SPLY = CALCULATE([Sale KG], FILTER(ALL('Calendar'), 'Calendar'[Fiscal Year No.] = SELECTEDVALUE('Calendar'[Fiscal Year No.])-1 && 'Calendar'[Fiscal Period No.] = SELECTEDVALUE('Calendar'[Fiscal Period No.])))
 
Wondering if there is a way I can write a formula to overcome those issues and truly display the YoY number throughout the period and for previous periods.
 
Thank-you in advance,
Michael
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hello All,

     

    I found a simple solution using the DATEADD function. We operate in 13 periods of 28 days, so DATEADD with 364 days does the tricks nicely.

     

    Thank you all for your contributions with inspired this solution.

     

    Michael

5 Replies

  • speedramps's avatar
    speedramps
    Super User
    6 years ago

    Hi MCKERY

     

    Please consider this solution and leave kudos:-

     

    Add a fiscal period offset column to your Calendar.  For example

     

    this period = -0

    last month =-1

    month before = -2

    month last year = -12

    next month = 1 

    next year = 12

     

    Then create measures:-

    Sales this period = Calculate([Sale KG],Calendar[fiscalperiodoffset]=0)

    Sales this period LY = Calculate([Sale KG],Calendar[fiscalperiodoffset]=-12)

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi speedramps, I am very interested to this solution. Could you please explain how the code for this new column should look like? Sorry, I am not so expert on DAX / M

       

      Thanks,

      C.

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You could try the following measure:

    Sale KG SPLY =
CALCULATE (
    [Sale KG],
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Fiscal Year No.]
            = MAX( 'Calendar'[Fiscal Year No.] ) - 1
            && 'Calendar'[Fiscal Period No.] = MAX( 'Calendar'[Fiscal Period No.] )
    )
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello All,

     

    I found a simple solution using the DATEADD function. We operate in 13 periods of 28 days, so DATEADD with 364 days does the tricks nicely.

     

    Thank you all for your contributions with inspired this solution.

     

    Michael