Measure to Calculate Sales from Previous year - Using a Datetable mapping current year to PY Date

[NiklasK]

Hello together,

I am struggling with the following problem.

There are two tables: Date and FactSales. The Date table shows the current date and has a mapped date to the previous year. So a simple DATEADD / PREVIOUSYEAR will not work...

I would like to create a matrix visual, which displays the following columns. (Sales PY (previous year) should not be empty)

Using the Selectedvalue function I can successfully map the Date PY to the current date:

However applying the same logic to the Calculate function ends up returning blank results. Anyone knows what I am doing wrong?

Thank you very much

[mh2587 · Accepted solution]

Just add LastYearDate column in Date Table and relate it to fact sale which will be inactive and then use the userrelationship() function to activate it virtualy in measure for PY
PreviousYearSales =
CALCULATE(
SUM(Sales[SalesAmount]),
USERELATIONSHIP('Date'[lastYearDate], Sales[SaleDate])
)

or try @johnt75 DAX

[johnt75]

Make sure that your date table has all the dates for all the years you are interested in, and make sure that it is marked as a date table.

Change the relationship between date and the fact table to single direction.

You can then write the sales PY measure as

Sales PY =
CALCULATE ( SUM ( 'Fact_Sales'[Sales] ), DATEADD ( 'Date'[Date], -1, YEAR ) )

[NiklasK]

I am not looking for an exact comparison but if you have a look at the calendar table, dates are shifted.