Create Filter for CALCULATE Where CY Sales AND PY Sales > $0 in Relevant Week Range

 RM Average Sales for Units With Sales > $0 =

-- Get the UnitIds.

VAR Units =

VALUES('Unit'[UnitId])

-- Get the Fiscal Week Num Overall for the Current Fiscal Week. This will be

-- an incomplete week (we always want the last completed week). On the Tuesday

-- morning refresh, the Fiscal Week Num Overall will increment to the next week

-- which has just begun.

VAR FiscalWeekNumber =

CALCULATETABLE(

    SUMMARIZE(

    'Calendar',

    'Calendar'[Fiscal Week Num Overall]

    ),

ALL('Calendar'),

    'Calendar'[Current Fiscal Week] = TRUE()

)

-- Get the list of the last 9 Fiscal Week End Dates prior to the current one.

-- This is the date range to check for Sales > $0.

VAR FiscalWeekEndDates =

CALCULATETABLE(

    SUMMARIZE(

'Calendar',

'Calendar'[Fiscal Week End Date]

    ),

ALL('Calendar'),

    AND(

        'Calendar'[Fiscal Week Num Overall] < FiscalWeekNumber,

        'Calendar'[Fiscal Week Num Overall] >= FiscalWeekNumber - 9

    )

)

-- Store the number of Fiscal Week End Dates. This is the number of weeks that a unit

-- needs to have where Sales are > $0

VAR NumberOfWeeks =

COUNTROWS(FiscalWeekEndDates)

-- Calculate CY Sales and PY Sales for each Unit in the relevant date range.

VAR WeeklyUnitSales =

CALCULATETABLE(

ADDCOLUMNS(

SUMMARIZE(

'Financial Summary',

'Unit'[UnitId],

'Calendar'[Fiscal Week End Date]

),

"CY Sales", [Sales],

"PY Sales", [Sales (PY)]

),

ALL('Calendar'),

FiscalWeekEndDates

)

-- Keep rows where CY Sales AND PV Sales are > $0.

VAR UnitSalesFilter =

FILTER(

WeeklyUnitSales,

AND (

[CY Sales] > 0,

[PY Sales] > 0

)

)

-- Calculate how many weeks have > $0 sales for each unit.

VAR UnitsWithSales =

GROUPBY(

UnitSalesFilter,

'Unit'[UnitId],

"@Weeks", SUMX(CURRENTGROUP(), 1)

)

-- Keep every Unit where every week sales are > $0.

VAR UnitFilter =

FILTER(

UnitsWithSales,

[@Weeks] = NumberOfWeeks

)

-- Calculate Average Sales; use UnitFilter as a filter.

VAR AverageSales =

CALCULATE(

[Average Sales],

UnitFilter

)

RETURN

AverageSales