Forum Discussion
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 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
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
4 Replies
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
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.
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
