SAMEPERIODLASTYEAR ISSUE

[Anonymous]

SAMEPERIODLASTYEAR  Issue

I have a very simple data set, that is a sub set of a larger data set where I am trying to create a measure to determine a value for same period last year.

Data basically has a product line, product line description, actuals data and I need to set same period last year to get actuals data for the same period last year.

Data set contains a few rows of data for Jan 15, 2021 and Jan 15, 2022

I have a valid date table that seems to meet these criteria:

To work with Data Analysis Expressions (DAX) time intelligence functions, there's a prerequisite model requirement: You must have at least one date table in your model. A date table is a table that meets the following requirements:

•             It must have a column of data type date (or date/time)—known as the date column.
•             The date column must contain unique values.
•             The date column must not contain BLANKs.
•             The date column must not have any missing dates.
•             The date column must span full years. A year isn't necessarily a calendar year (January-December).
•             The date table must be marked as a date table.

The date table in my data set is named :  vw_FiscalStartDate.
Main table with data is vw_PL_Analysis

Vw_PL_Analysis has a column named pbixDate which contains full dates for transactions for the period.

I have a relationship between this column and the FUllDate column in vw_FiscalStartDate.

I have created a measure that returns GL Actuals in one column.  This seems to work.  I have calculated SAMEPERIODLASTYEAR             trying to use this measure as a variable and using the column identified in my data.  (See solution).

I either get one of three consistent responses. 

1. Prior year = EMPTY
2. Prior year = THIS SAME PERIOD DATA
3. Prior year – it adds a row for a future date and gives this years values.

I am struggling here and cannot get this to work.

My solution is in a ZIPPED PBIX file located here:

https://tinyurl.com/589df3yj

Can anyone please help?

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could cahnge the measure .

PriorYRActuals2 = 
CALCULATE(SUM('vw_PL_Analysis'[ActualsBookUSD]),SAMEPERIODLASTYEAR('vw_PL_Analysis'[pbixDate]),ALL(vw_FiscalStartDate))

The final output is shown below:

Or change the visual.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

Your 3rd measure is the correct one. The reason that it is adding a row with a future date is that the measure produces a non-blank result for that date, which is the value from this year.

To prevent future dates showing up there are a couple of options, at least. You could add another column onto your calendar table like

Before Today = IF( 'Date'[Date] < TODAY(),1, 0)

and then use that as a visual level filter, or use it inside the measure itself to only return a value when Before Today is 1. Personally I would choose the visual level filter.