Forum Discussion
Measure to Calculate Sales from Previous year - Using a Datetable mapping current year to PY Date
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
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
3 Replies
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 DAXMake 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 ) )I am not looking for an exact comparison but if you have a look at the calendar table, dates are shifted.
