Amount for Same Day Last Year

[kressb]

I have a 3 tables set up like so:

I want to make a table that shows the Amount for the Same Day Last Year, ex:

I have tried:

AmountPY = Calculate(sum(DataTable[Amount]),Sameperiodlastyear(DateDimension[Date]))
The error states: Function Sameperiodlastyear expects a contiguous selection when the date column comes from a table on the 1-side of a bi-directional relationship. ??

AmountPY= Calculate(sum(DataTable[Amount]),

                                    Filter(DateDimension,

                                             Date(
                                              Year(DateDimension[Date]-1),
                                               Month(DateDimension[Date]),
                                                Day(DateDimension[Date])
                                                       )))
Returns same # as Current Year

What am I missing?

[kressb]

@Jihwan_Kim @Ashish_Mathur 
I had a Many:Many Relationship between the DateDimension and a different table.

I changed the filtering from both to Single DateDimension filters DifferentTable.

That seems to have fixed the issue.

I am completely self taught and do not understand how this relationship created such an issue.

Would anyone be willing to post some articles of learning?
All i know is to avoid Many:Many as much as possible. TYIA.

[Jihwan_Kim]

Hi, @kressb 

I have tried your first calculated measure and it worked.

I hope your dim date table is continuous and marked as a date table.

thank you.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[kressb]

@Jihwan_Kim it is marked a date table.

What is meant by "continuous"?
DateDimension[Date] is calculated =Calendar(Date(2019,07,01),Date(2022,12,31))

[Ashish_Mathur]

Hi,

Both relationships should be Many to One and Single.  Drag Date from the Date Table.  Write these measures:

Total = sum(data[amount])

Total in SPLY = calculate([Total],sameperiodlastyear(calendar[date]))

If this does not work, then share the link from where i can download your PBI file.  

[kressb]

@Ashish_Mathur 
Unfortunately cannot share file.
I pulled out the 3 tables in question (there are 42 tables in this build) and it works fine on it's own.

Could an appended query cause any issue with this?
Each year of Amounts is in a separate table (DataTable_2020, DataTable_2021), which I appended as a new query - DataTable_ALL