Same Period Last Year breaks with non contiguous month values

[fb198]

Hi,

I'm trying to calculate the previous year's sales for selected months of the current year.
My LY Sales measure looks like this:
CALCULATE( [Total Sales], SAMEPERIODLASTYEAR(Calendar_Table[Date]))

I have Year and Month slicers on the page. When I choose a single month the measure works perfectly, or even months in continuity (Oct, Nov, Dec). However when I choose non continuous values like July and December, the measure breaks.

It should normally just be picking the previous year sales of the selected months and adding it up. Do you know what is the issue here and how it can be solved?

[Anonymous]

Hi @fb198 , hello speedramps  and johnt75, thank you for your prompt reply!

Based on your description, I have created a sample PBIX file for your reference.

Please check the attached file to see if it provides any clues for troubleshooting.

If you need further assistance, feel free to ask!

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[speedramps]

@fb198 

I am not convinced you are providing us with with the correct relationship because the error message says you have bidirectional relationship.

Have you got a list of contigous dates in the calander for TY and LY?

I supect you either sending us the wrong relationship, or you have the wrong table column in your visual or there are other DAX measure or filters in your visual.

Try create a new report page, with new visual and slicer (dont copy and paste).

Add each item cartefully and try replicate the error.

[speedramps]

Check your relationship between the Calendar and Fact table.

It should be 1:M with Single cross filter direction

I suspect you have a 1:1 relationship with Both which causes the contiguos error 

Please click [accept solution] and thumbs up. Thank you.

[fb198]

Thank you for your reply.

My Calendar table has 1:* relationship with the Sales Table. The calendar is also marked as the Date Table.

On choosing a single month

When I choose non contiguous months:

This is the error I'm seeing.

Thanks in advance for any help.

Kind Regards.

[johnt75]

I don't think that the problem is with the SAMEPERIODLASTYEAR function, that can handle discontiguous dates.

First check that your calendar table is marked as a date table, then you can run in DAX Query View

DEFINE
VAR _ThisYear = CALCULATETABLE(
	VALUES( 'Date'[Date] ),
	TREATAS( { DATE( 2025, 1, 1 ), DATE( 2025, 3, 1 ) }, 'Date'[Start of Month] )
)
VAR _LastYear = SAMEPERIODLASTYEAR( _ThisYear )

EVALUATE
ROW(
	"this year", COUNTROWS( _ThisYear ),
	"last year", COUNTROWS( _LastYear ),
	"min last", MINX( _LastYear, 'Date'[Date] ),
	"max last", MAXX( _LastYear, 'Date'[Date] )
)

Obviously you'll need to change the TREATAS to match your model and the dates you're having problems with. You should see the same row count for both this year and last year.

[speedramps]

@johnt75  Thanks for the answer but I suspect that the relationship is wrong because it is generating a "contiguos" error.  That suggests it is a problem with the relationship rather than the dax measure. See my suggestion on how to fix it. 😀