Help with Average Weekly DAX formula by comparing same period last year

Hi echow 

I think you have related your sales table and date table by calendar date column.

Firstly add a Yearweek column in SalesTable.

YearWeek = RELATED(DateTable[C_YEARWEEK])

Due to you want to compare values(divide sales with count) in select year with last year, it is better to build a slicer table(Yearweek) which is not related to other tables.

YearWeek = VALUES(DateTable[C_YEARWEEK])

Then you can achieve your goal by measures.

Sales in Bar = 
VAR _SelWeeknum =
    VALUES ( YearWeek[C_YEARWEEK] )
VAR _Sales =
    CALCULATE (
        SUM ( SalesTable[ Sales ] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _SelWeeknum
        )
    )
VAR _Count =
    CALCULATE (
        DISTINCTCOUNT ( SalesTable[MBR_NO] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _SelWeeknum
        )
    )
RETURN
    DIVIDE ( _Sales, _Count )

LY Sales in Bar = 
VAR _SelWeeknum =
    ADDCOLUMNS ( VALUES ( YearWeek[C_YEARWEEK] ), "LY Weeknum", [C_YEARWEEK] - 100 )
VAR _LYWeeknum =
    SUMMARIZE ( _SelWeeknum, [LY Weeknum] )
VAR _LYSales =
    CALCULATE (
        SUM ( SalesTable[ Sales ] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _LYWeeknum
        )
    )
VAR _LYCount =
    CALCULATE (
        DISTINCTCOUNT ( SalesTable[MBR_NO] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _LYWeeknum
        )
    )
RETURN
    DIVIDE ( _LYSales, _LYCount )

Result is as below.

Select 202042 and 202043

Select 202043

You can download the pbix file from this link: File

Best Regards,

Rico Zhou

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