Sum values based on custom dates from a separate table

[Anonymous]

Hi - I'm completely new to Power BI and DAX formulas. I've searched online for solutions to this issue but cannot get it to work. The solution may be really simple but I might have overcomplicated things.

I have a lot of tables but for simplicity sake, my dataset is setup like this:

1. Sales Table with [Date], [Sales Amt]
2. Calendar table with custom mapped dates [TY_WE_DATE], [LY_WE_DATE] <-- these are based on weeks (multiple days fall into these weeks)

Here is an example of what a piece of the calendar table looks like: 

I'm try to sum [Sales_Amt] from the sales table for LY_DATE. In the below screenshot, I have my TY(WE_DATE) and its corresponding LY_WE_DATE. The sales table is by day. I'm able to aggregate the Net_Sales_Amt using the WE_DATE. But I can't seem to figure out how to sum the corresponding Net_Sales_Amt using the LY_WE_DATE. 

I need to keep it in this format because I'm filtering on a relative date to show the last 30 days only for this report vs create a new table.

So as an example, Let's take WE Date 2021-02-07 

• The days in 2021-02-07 are 02-01 through 02-07
• The days in the corresponding LY WE Date is 2020-02-03 through 2020-02-09. 

I now want to sum all the values in the LY dates and have it appear as another column called LY_NET_SALES_AMT

How do I calculate a measure for the LY dates based on values from the calendar table?

Sorry if this sounds confusing! I hope this makes sense to someone! 

[DataInsights · Accepted solution]

Hi @Anonymous,

Try this measure:

LY Net Sales Amount =
VAR vLYDate =
    MAX ( Dates[LY_WE_DATE] )
VAR vResult =
    CALCULATE ( SUM ( Sales[Sales_Amt] ), ALL ( Dates ), Dates[WE_DATE] = vLYDate )
RETURN
    vResult

[Anonymous]

@DataInsights Wow - that worked! I definitely would not have figured this one out. Thank you for your help!

[DataInsights]

Great! The key is clearing the date filter context with "ALL ( Dates )" so you can filter WE_DATE.