How to calculate for an alternate date (same fiscal day last year)?

[rlupe]

What DAX formula can I use to sum sales for the same fiscal day last year? My date table has a column fiscaldaylastyear_date, so if my data is on 1/1/2023, the fiscal day last year column would be 1/2/2022.

I want to be able to show the fiscal last year sales on the corresponding date.

[JohnnySaade]

Create an inactive relationship between your sales table date column and the calendar table fiscal date LY column. Then write this measure:

CALCULATE( SUM(Sales[Sales Amount]), USERATIONSHIP(Sales[Date],Calendar[fiscalDateLY]) )

this will make sure that your sales will always recalculate according to the fiscal date last year column

[rlupe]

I made the relationship, but it is still displaying the This Year sales. Might have to do that I have to use Many to Many relationship instead of Many to One due to blanks in the fiscal day last year column. 

[amitchandak]

@rlupe , Make sure you have date table joined with date of your table and then try

Example measure

week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[rlupe]

This does work, however I was trying to do something more dynamic because I need to do this for 15 sales measures and would have to adjust each next year with the new calendar.