Forum Discussion

Jidnyasa2904's avatar
Jidnyasa2904
Icon for Helper I rankHelper I
2 years ago
Solved

Dynamic 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 w...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    Jidnyasa2904
    Icon for Helper I rankHelper I
    2 years ago

    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