Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

How to use dateadd with lookup

Hi,

 

We have a database with snapshots. We want to compare growth from previous week to the next (based on the week selected). Ie: Rev at week 23 was 100,

Rev at week 22 was 90

Hence Rev sold last week was 10.

 

 

However these weeks are selected in report by using this financial week field "FY19 WK23" which is Financial Week 23 in financial year 19.

 

This Week 23 has a date (9th December) in my date table.

 

When using DATEADD in Dax I have my formula working using the snapshot_date (in a date format), however I would like to use the "FY19 WK23" format as that is how the analysts work.

Can I use some sort of look up or similar to provide the correct formula that DATEADD requires?

 

Formula currently:

 

 

 

Rev TY as at Last Week  =
CALCULATE( [Rev TY meas v2],
      DATEADD(DIM_SNAPSHOT_DATE[SNAPSHOT_DATE], -7, day))
 
Need Help

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, Anonymous

    Is this formula what you want to get?

    Rev TY as at Last Week  =
    CALCULATE( [Rev TY meas v2],
          DATEADD(DIM_SNAPSHOT_DATE[WeekNum], -7, day))
     
    It couldn't be achieved, for this is a datetime function, "FY19 WK23"  is just a text field, you could use "FY19 WK23" in the visual
    to disappear but can't use it in the formula.
     

    Best Regards,

    Lin