Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

SAMEPERIODLASTYEAR Returns values for future date

I am using SAMEPERIODLASTYEAR to create a measure that returns last years sales values. However my Date Table also has future dates for forecasting purposes.   My question is how do i prevent the f...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi Anonymous -

    You're on the right track with using SAMEPERIODLASTYEAR, but the issue is that SAMEPERIODLASTYEAR still returns dates from the prior year, even when you're in a future month (with no current year data yet).

    To fix this, we need to ensure that the prior year values are only returned when the current period has actually occurred

     

    Prior Period Sales =
    VAR CurrentDate = MAX(DateTable[Date])
    RETURN
    IF (
    CurrentDate <= TODAY(), -- Only calculate for dates up to today
    CALCULATE(
    SUM(SalesData[Sales]),
    SAMEPERIODLASTYEAR(DateTable[Date])
    )
    )

     

    can you try this and confirm

BI_Maverick's avatar
BI_Maverick
Regular Visitor
1 year ago

Hi Anonymous ,

 

 
Prior Period Sales = CALCULATE( SUM(Sheet1[Sales]), FILTER( SAMEPERIODLASTYEAR(DateTable[Date]), DateTable[Date] <= TODAY() ) )

Could you please try the measure above instead? 


The key is to apply the future date filter directly to the table returned by SAMEPERIODLASTYEAR. This would force the measure to ignore any last‐year dates after today. 

 

Could you please give it a try and confirm if it solved the issue?

 

Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi BI Maverik 

     

    Thanks you trying to help. Unfortunately you measure resulted in future dates having values. This is the issue I have been having with various versions of the formula. For some reason a filter is not being implemented to restrict the dates.