Forum Discussion

vijay273162's avatar
vijay273162
Icon for Helper III rankHelper III
4 years ago
Solved

SAME PERIOD LAST YEAR

Hi All,

 

basic Question from my side.

 HOW TO USE SAME PERIOD LAST YEAR  ---- in ----- calculated columns ?

 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi vijay273162 

    If you are using dates from a fact table, try this way,

    column = 
VAR _date =
    EDATE ( 'Table'[Date], -12 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] >= _date
                && 'Table'[Date] < EARLIER ( 'Table'[Date] )
        )
    )

    If you want to create measure, then try this

    LY = CALCULATE(SUM('Table'[Sales]),SAMEPERIODLASTYEAR('Table'[Date]))

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

11 Replies

  • truptis's avatar
    truptis
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi vijay273162 

    SAMEPERIODLASTYEAR - Returns a table that contains a column of dates shifted one year back in time from the dates specified in date column.
    Thus, you can't use it in the column.

    you can achieve this using the below steps:
    1- For the current date - create 1 concatenated column of date removing '/'. you can add a custom column which says:
    Use CONCAT function : eg - =Concat(Month(Date),Year(Date))
    2- For the last year's date - create another concatenated column 
    eg- Concat(Month(Date),year(Date)-1)
    3- Return the values of sales by matching column 2(Concatenation of Last year's date) with Column 1(Concatenation of current date).

    If you need the exact DAX, then please share sample data with me. 

    If this helps, then please mark it as a solution and hit the thumbs up.
    Regards,

    TruptiS

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Perfect

    • vijay273162's avatar
      vijay273162
      Icon for Helper III rankHelper III
      4 years ago

      Hi,

       

      This is how I got the result for Same Period Last Year using calculated column

       

       

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi vijay273162 

    If you are using dates from a fact table, try this way,

    column = 
VAR _date =
    EDATE ( 'Table'[Date], -12 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] >= _date
                && 'Table'[Date] < EARLIER ( 'Table'[Date] )
        )
    )

    If you want to create measure, then try this

    LY = CALCULATE(SUM('Table'[Sales]),SAMEPERIODLASTYEAR('Table'[Date]))

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

    • YoungLearning's avatar
      YoungLearning
      Icon for Helper III rankHelper III
      2 years ago

      Is SUM necessary?

       

      This answer looks wrong too, can someone educate me a little here? I was assuming it's SAMEPERIODLASTYEAR.

       

  • truptis's avatar
    truptis
    Icon for Community Champion rankCommunity Champion
    4 years ago

    vijay273162 

    Please mark this as a solution if it worked for you so that other community members can find the solution quickly. Thanks.

     

    Regards,

    TruptiS

  • Ikalaus's avatar
    Ikalaus
    Frequent Visitor
    2 years ago

    I have another solution for how to calculate last year same-month 

     

    Last year revenue = CALCULATE(
        SUM( 'table'[revenue]),
        FILTER(
            'table',
            'table'[month/date] = DATE( YEAR(TODAY()) - 1, MONTH(TODAY()), 01)
               
        )