How to lookup a different column to calculate the sum with slicer selected multiple values in DAX

[tiny]

Hello, 

I currently need help to calculate a Sales for the selected dates's last year's Fiscal date: here is an example, suppose we have a date table:

Then we have a slicer based on the Date table with fiscal year, period, week hierarchy and can select multiple values:

And we have a Sales table

Date table and Sales table has 1:* relationship with Calendar Date and Sales Date

Now  what we want to do is to create a measure, when the values selected in the Slicer, we want to get the sum of the corresponding sales of LY Fiscal dates, for example, in the slicer, Fiscal year 2022, period 2 and 3 are selected, the dates (20211007, and 20211018) are related in Sales table, we need to get the sum of the sales of LY Ficscal dates (20201008 and 20201019) for these dates,  total will be 160.  

Do you have any idea or suggestions on how to achive this? Thank you very much! Really appreciated!

[tiny]

Just found your solution worked with a small amont of test data and tables, so it should be the right solution, I just need to dig into my data to see what's wrong. Thanks again, really appreciated it! 

[tharunkumarRTK]

@tiny 

Assuming you have date key column and date column in your date table, and user selects the dates sequentially. Capture the user selection and then calculate the previous year value

[Measure] = 
Var __dates = values(datetable[date])
Var result = calculate([SalesAmount], dateadd(__dates, -1, year))
return result 

or if you do not have any date column then 

[Measure] = 
Var __mindate = Min(dateTable[dateKey]) - 10000
Var __maxdate = Max(dateTable[dateKey]) - 10000
Var result = calculate([SalesAmount], datetable[dateKey] > __mindate, datetable[dateKey] < __maxdate)
return result

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[tiny]

Thank you so much Tharun!

It's a great suggestion, but the problem here is the LY fiscal date is not calculatable, we have to use the column to retrieve the date... do you have any suggestion to use the column instead of calculation of the date?

[tiny]

To be clear, we have to use the column 'LY Fiscal Date" to get the sales from the above example, thanks again for your help!

[tharunkumarRTK · Accepted solution]

@tiny 

If my understanding is correct then you have a table with 'actual date' and corresonding 'LY Fiscial Date' columns. And when ever user selects a actual date in a slicer then the sale value corresponds to the 'LY Fiscal date' should appear. (but nor based on actual date column) 

You can achelive it by establishing two relationships between sales and date table one active relationship between sales[date] <- date[actual date] and one inactive relationship sales[date] <- date[LY fiscal date]. 

your measure definition should be:

Calculate([Slaes Amount Measure], userelationship(sales[Date], date[LY Fiscal date]))

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[tiny]

Thank you so much Tharun! Really appreciated it.

That's a great idea, I just gave a try, it is strange it returns the results as what we get for the current date, we need to get both current date sales and LY sales at the same time, my measues are like the following:

current sales = CALCULATE(sum('Sales'[Sales Amount]))

LY sales = CALCULATE(sum('Sales'[Sales Amount]), USERELATIONSHIP('Calendar'[LY Fiscal Date], 'Sales'[Sales Date]))

I got the exact same results for both as current sales, it seems it only uses the active relationship... Any idea what could be wrong? Thanks again!

[tharunkumarRTK]

@tiny 
can you share the PBIX file with sample data?