DateAdd with a Date Table Not Working

[Mat42]

This sort of problem appears over and over again online, but I can't find anything that answers my question, so apologies if this repeats anything posted previously.

We have a large report that is being driven by a date table. The date table, helpfully named 'DateTable' is a dax table. It has been marked as a date table. By 'driven' I mean that all other tables have a relationship with it and it will form the basis for almost all interaction via a dropdown, and is used in other calculations.

So, I have a monthly sum dataset. This dataset is just a simple table which calculates a total for the month from another table. So, when added to a table visual, it basically looks like this:

What I need to be able to do is have a column on a table visual that shows the same period the previous year. I created a measure to do this, which looks like this:

I know I can use the 'SamePeriodLastYear' function, but it'll be used to go back further, so I subtracted a year instead. It does what I would expect and my table looks like this:

You can see that the new column displays 77 for 30/04/2024 because the previous period end, 30/04/2023, was 77. And there is nothing for 30/04/2017 because the data doesn't go any further back.

But, I need the DateTable table to be used, so I created a new measure by copying the previous one and replacing the references, and it looks like this:

But it doesn't work. When I add it to the table visual it just displays the same years' data but omits the most recent:

I've found countless guides and examples online that describe the same basic issue, but none that display the same result.

What am I doing wrong and how stupid am I being?

[HotChilli]

'Period End' in the visual - is that from the date table or the Fact table? 

[Mat42]

Period End is from the fact table. It's a basic table of totals for a month.

So, we have a table that details figures for each day of the month, then this table is just a total of those figures. The dates in the table are all the last days of each month, with a total for that month. That first image is exactly how the fact table looks:

There are no 'in between' dates, it's just the last day of each month for multiple years. In the example it's the years 2017-2024 for March and April.

[Anonymous · Accepted solution]

@HotChilli  Thanks for your contribution on this thread.

Hi @Mat42 ,

What's your expected result? Do you want to get the values for the previous year and apply the date field of DateTable on the visual to replace the field [Period TA] of table? I created a sample pbix file(see the attachment), please check if that is what you want.

Best Regards