measure to return a value from a column with date offset

[Anonymous]

I'm trying to create a measure that allows me to return a value from a column in my facts table but filter it by current day offset from my dates table. I want to display this in a card. 

So I want to show the daily total from the same day last week. then create another card that shows same day two weeks ago and so on up to the previous same day 4 weeks. 

my facts table also has a date column. 

These are the two tables and column names.

'facts table' [daily total]
'Date' [CurrDayOffset]

Please can someone help me. 

[Anonymous]

@Anonymous Thank you. I frustrated at how easy that it! I spent hors messing with various functions. 

Thank you. 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try measure as below to calculated the daily total in previous same day n weeks.

Previous 1 Week Same day =
CALCULATE (
    SUM ( 'facts table'[daily total] ),
    FILTER ( ALL ( 'Date' ), 'Date'[Date] = SELECTEDVALUE ( 'Date'[Date] ) - 7 )
)

Previous 2 Weeks Same day =
CALCULATE (
    SUM ( 'facts table'[daily total] ),
    FILTER ( ALL ( 'Date' ), 'Date'[Date] = SELECTEDVALUE ( 'Date'[Date] ) - 7 * 2 )
)

...

Previous N Weeks Same day =
CALCULATE (
    SUM ( 'facts table'[daily total] ),
    FILTER ( ALL ( 'Date' ), 'Date'[Date] = SELECTEDVALUE ( 'Date'[Date] ) - 7 * N )
)

My Sample:

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.