Dynamic period shift for TY & LY Comparison

[Jidnyasa2904]

My matrix has event data, the days between events TY and LY vary. So, I have created this date shift column from which if I select 8, the values from LY will shift 8 days and match the TY time. 
I want to make this dynamic. 

Any leads are welcome. Thanks in advance!

[Anonymous · Accepted solution]

Hi  @Jidnyasa2904 ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure =
var _select=SELECTEDVALUE('Data Shift Table'[Data Shift])
return
SUMX(
    FILTER(ALL('Test_Table'),
    'Test_Table'[Date  TY]=MAX('Test_Table'[Date  TY])-_select),[LY])

2. Result:

If the results above don't meet your expectations, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

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

[Jidnyasa2904]

@Anonymous Hi Yangliu, 

Thank you for helping with the date shift. But I am facing a problem when I Drill up the date column. 
I drill up to check how I performed weekly. And it gives an average value instead of SUM for that particular week. 
The orange color column is not drilled up correctly. (Thats the same measure as above solution)
1. Drilled

2. Drill Up