SAMEPERIODLASTYEAR not showing all records

[TradSag]

Hi Team,

I have found a not expected behaviour using SAMEPERIODLASTYEAR function.

Context:

• Account table containing fields: Date, Account, Account_Group and Amount fields.
• Calendar table generated based on Account table Date field. This table is marked as Date table and linked to Account table.

I'm trying to compare the vaule from currently selected date versus previous year same value. For this I'm using SAMEPERIODLASTYEAR function in the following way: 

PY_Amount = CALCULATE(Sum('Account'[Amount]);SAMEPERIODLASTYEAR('Calendar'[Date]))

Issue:

As an example, selecting May 2019, this is the result:

If we check May data separatelly by year 2018 and 2019, we find the following:

As you can check in the initial image (the one that shows Amount and PY_Amount), we don't find the record corresponding to account 88170000000 marked with a red box in 2018 image.

I assume that the record is not shown because we it does not exist in 2019 context, but how can we avoid this behaviour?

Thanks for the support,

Best regards

Best regards

[TradSag · Accepted solution]

Hi,

I have solved the issue.

I removed the configuration that makes Calendar table into Date table and use the formula as following:

PY_Amount = CALCULATE(Sum('Account'[Amount]);SAMEPERIODLASTYEAR('Calendar'[Date].[Date]))

Adding .[Date] option makes the issue to be resolved.

Thanks and regards

[vanessafvg]

try putting an all in

test =
CALCULATE (
    SUM ( 'Account'[Amount] ),
    ALL ( 'Calendar'[Date] ),
    SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)

[TradSag]

Hi,

Thanks for the answer.

Unfortunately, this is not working. I'm getting same the exactly the same result.

Any ideas?

Thanks and regards