how to get 52 weeks/YAGO data using DAX

[smeg]

Hi I have below measure which is working perfectly fine and giving expected results.

12wks Dollars:=CALCULATE([Dollars],FILTER(Metrics_Table, Metrics_Table[Agg_Level]= 12))

But when I try to write below measure  12wks Dollars year ago it is throwing an error,

12wks Dollars YAGO:=CALCULATE([Dollars],FILTER(Metrics_Table, Metrics_Table[Agg_Level]= 12 &                                        Metrics_Table[AO_Date]=dateadd(Metrics_Table[AO_Date],-364,Day)))

Also I have dates_table too with Date_Anchor_YAgo field(exactly 52 weeks back date  as shown below,

Metric table and date table are both having relationship.

If it is better to  use Date_Anchor_YAgo field from dates table ,how can i write my 12wks Dollars YAGO measure correctly?

Thanks..

[Anonymous · Accepted solution]

Hi @smeg,

You can try to use the following measure formula if it works on your side: (I modify the DATEADD function to use with extracted calendar date values as condition in formula calculations)

12wks Dollars YAGO :=
VAR currDate =
    MAX ( Calendar[Date] )
RETURN
    CALCULATE (
        [Dollars],
        FILTER (
            ALLSELECTED ( Metrics_Table ),
            Metrics_Table[Agg_Level] = 12 & Metrics_Table[AO_Date]
                = DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
        )
    )

Regards,

Xiaoxin Sheng