SAMEPERIODLASTYEAR Returns values for future date

[MichaelMAU]

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 following formula returning values for future periods.

 Here is the formula i have used.

PRIOR_PERIOD_SALES =
CALCULATE(
SUM(Sheet1[Sales]),
SAMEPERIODLASTYEAR(DateTable[Date])
)

I have then tried various versions of it to filter out future dates to no avail.

Here is an example of one formula but still it gives the same result

Prior Period Sales = CALCULATE(
SUM(SalesData[Sales]),
SAMEPERIODLASTYEAR(DateTable[Date]),
DateTable[Date] <= EOMONTH(TODAY(), 0),
DateTable[Date] <= TODAY()
)

Below is a screenshot of the result with the undesired part highlighted in yellow

I would have thought that SAMEPERIODLASTYEAR would have the logic to not repeat this pattern beyond what is current.

I would greatly appreciate help with a formula that filters out future dates in this measure. 

Thank you

[rajendraongole1 · Accepted solution]

Hi @MichaelMAU -

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

[MichaelMAU]

Hi rajendraongole1,

Thank you for assisting with this. This works! Thank you very much. It appears that the Variable is required to define a date range first before moving onto the SAMEPERIODLASTYEAR component of the measure.

[BI_Maverick]

Hi @MichaelMAU ,

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

[MichaelMAU]

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.