Forum Discussion
Helper IDynamic period shift for TY & LY Comparison
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!
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
2 Replies
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. Drilled2. Drill Up
