Forum Discussion

STS_Joshua's avatar
STS_Joshua
Icon for Helper II rankHelper II
6 years ago
Solved

Trying to calculate SAMEPERIODLASTYEAR

Hi all, I recieve weekly sales data every saturday. I am looking for a way to compare this weekly data with the same weeknum from last year. SAMEPERIODLASTYEAR on a date table isn't doing the trick. ...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try this measure:

    Measure = 
VAR LastYearSales =
    CALCULATE (
        SUM ( 'Table'[net_sales_retail] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[wk] = SELECTEDVALUE ( 'Table'[wk] )
                && YEAR ( 'Table'[period_date] )
                    = YEAR ( SELECTEDVALUE ( 'Table'[period_date] ) ) - 1
        )
    )
RETURN
    IF (
        YEAR ( MIN ( 'Table'[period_date] ) )
            = YEAR ( MINX ( ALLSELECTED ( 'Table' ), 'Table'[period_date] ) ),
        BLANK (),
        ( MAX ( 'Table'[net_sales_retail] ) - LastYearSales ) / LastYearSales
    )

    The result shows the increase/descrese compared with the last year same weekNo data:

     

    Best Regards,

    Giotto Zhi

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Week year is 364 days behind with date calendar you can use

Week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

STS_Joshua's avatar
STS_Joshua
Icon for Helper II rankHelper II
6 years ago

That will not work because I want to compare the value I have for 3/14/2020 with the value I have for 3/16/2019.

 

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    STS_Joshua,

     

    If you need the see 1 year ahead, just change to:

    SamePeriodoLastYear =
    VAR _week = SELECTEDVALUE('Table (2)'[WeekNumber])
    VAR _year = YEAR(SELECTEDVALUE('Table (2)'[period_date])) + 1
    return CALCULATE(SUM('Table (2)'[net_sales_retail]); FILTER(ALL('Table (2)'); YEAR('Table (2)'[period_date]) = _year && 'Table (2)'[WeekNumber] = _week))
     
    If not, keep the same code, it's gonna bring the value for last year and same week.