DAX Formulas for normalising refurbishment days and doing YoY comparison

[CC125]

I have a table of unpivoted daily sales data structured like below. The date column has date data from 23/12/2023 to 24/10/2025. The refurbishment date as you can see is currently repeated in every row, even though there's only one refurbishment date per store.

I want to be able to get a view of the YoY change in sales, by week from refurbishment. I don't want the store level detail, I want the average of all stores. For example on the output I'll be able to see a trended view of WeekFromRefurbishment at week -15, all the way through to week 0 (week of refurbishment) and then week 1, 2, 3 etc as we get further from refurbishment. I'm expecting I'll be able to see a trend of YoY performance improving from pre-refurbishment weeks to post refurbishment.

For the YoY comparison I'll need to find the date of refurbishment, let's take store 1 below and say 22/08/2025 that will be within week 0. I then need to compare the sales for week 0 2025 vs the equivalent week in 2024 (so near 22/08/2024).

I'm confused how I'd approach this 

Store No	Refurbishment Date	Date	Sales
1	22/08/2025	23/12/2023	80
1	22/08/2025	24/12/2023	70
1	22/08/2025	25/12/2023	50
2	29/07/2025	23/12/2023	100

[v-tsaipranay]

Hi @CC125 ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @CC125 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-tsaipranay · Accepted solution]

Hi @CC125 ,

Please use the below dax for clearing  "A function 'PLACEHOLDER'  error : 

Avg YoY % per Store =
VAR ActiveStores =
    FILTER(
        VALUES('Sales'[Store No]),
        NOT ISBLANK([YoY %])
    )
RETURN
    AVERAGEX(ActiveStores, [YoY %])

If you don’t need the ActiveStores variable, you can also simplify it by following : 

Avg YoY % per Store =
AVERAGEX(
    FILTER(
        VALUES('Sales'[Store No]),
        NOT ISBLANK([YoY %])
    ),
    [YoY %]
)

I am also inculding the pbix please have a look and please feel free to reach out for any further assistance.

Thank you.

[v-tsaipranay]

Hi @CC125 ,

Thank you for the update. That error normally occurs when table expressions are being created as standalone measures. To avoid more back-and-forth on syntax variations, the next step is to validate this against your actual data model structure.

If that doesn't help, please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

[CC125]

Thanks, I've put together some sample data, showing the data structure as it is before uploading to Power BI. My goal is to visual the average (not weighted) YoY sales change of all the stores from -24 weeks out to Go Live Date to whatever the latest week is since go live. A stores mobilisation week should be 0. I'd like the data displayed with weeks on the x axis (-24 weeks through to whatever the latest week is) and the Y axis is the given weeks sales vs the same week last year, as a flat average for all stores.

Here is the sample data: it wouldn't let me paste more than 20k characters so I had to hide most of the rows, the data runs the same period for each store, 23/12/2023 to 20/10/2025 and I've used the Excel formula RANDBETWEEN(3,7) to get the daily sales sample data. Hopefully it's very quick to replace the rows I had to remove to paste here 

Store No	Go Live Date	Date	Sales
1	24/09/2025	23/12/2023	6
1	24/09/2025	24/12/2023	7
1	24/09/2025	25/12/2023	5
DAILY DATA CONTINUES
1	24/09/2025	19/10/2025	6
1	24/09/2025	20/10/2025	3
2	24/11/2025	23/12/2023	7
2	24/11/2025	24/12/2023	3
2	24/11/2025	25/12/2023	6
2	24/11/2025	26/12/2023	5
2	24/11/2025	27/12/2023	6
DAILY DATA CONTINUES
2	24/11/2025	16/10/2025	6
2	24/11/2025	17/10/2025	3
2	24/11/2025	18/10/2025	6
2	24/11/2025	19/10/2025	3
2	24/11/2025	20/10/2025	3
3	01/07/2025	23/12/2023	4
3	01/07/2025	24/12/2023	3
3	01/07/2025	25/12/2023	4
DAILY DATA CONTINUES
3	01/07/2025	18/10/2025	7
3	01/07/2025	19/10/2025	4
3	01/07/2025	20/10/2025	7

[v-tsaipranay]

Hi @CC125 ,

Thank you for reaching out to the Microsoft fabric community forum and for the update and clarification.

The behavior you are experiencing occurs because AVERAGEX(Stores, …) calculates across all store rows, even if a particular Weeks From Refurb value is missing for some stores. To ensure the average only includes stores with data for that specific week offset, you should filter the store set to include only those stores that have data for the current week context.

Try using the following pattern:

Active Stores =
CALCULATETABLE(
    VALUES('DailySales'[Store No]),
    NOT ISBLANK([Total Sales])
)

Avg Sales per Store =
AVERAGEX(
    [Active Stores],
    [Total Sales]
)

Avg PY Sales per Store =
AVERAGEX(
    [Active Stores],
    [PY Sales]
)

When you use Active Stores, only stores with sales in the current Weeks From Refurb context are included in the average. For example, at Week 17, only stores that have reached 17 weeks post-refurb are counted.

The average calculated is a simple arithmetic mean and does not account for store sales volume.

After updating the measures, the values will adjust correctly when you filter the visual by a specific week offset.

Hope this helps. Please reach out for further assistance.

Thank you.

[CC125]

I get the error "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed." when trying to create a measure or table with this

[lbendlin]

 I then need to compare the sales for week 0 2025 vs the equivalent week in 2024 

You will either need to have a Calendar table that clearly indicates the week numbers per year,  or you will have to subtract 364 to arrive at the "same"  week day a year prior.

[DataNinja777]

Hi @CC125 ,

In order to solve your problem, you can first create a calculated column in your Sales table like below:

Weeks From Refurb = 
VAR RefurbDate = RELATED(Stores[Refurbishment Date])
VAR DaysDiff = 'Sales'[Date] - RefurbDate
VAR WeekNum = INT(DaysDiff / 7)
RETURN WeekNum

Then create measures like below:

PY Sales = 
// 1. Get the dates from the Sales table that match the current axis filter
//    (e.g., all dates for "Week -100")
VAR CurrentDatesInSales = VALUES(Sales[Date])

// 2. Get the equivalent dates in the prior year
VAR PYDates = SAMEPERIODLASTYEAR(CurrentDatesInSales)

// 3. Calculate sales, REMOVING the axis filter but APPLYING the new PYDates filter
VAR Result = 
    CALCULATE(
        [Total Sales],
        PYDates,
        REMOVEFILTERS(Sales[Weeks From Refurb])
    )
RETURN
    Result

Avg Sales per Store = 
AVERAGEX(
    Stores,
    [Total Sales]
)

Avg PY Sales per Store = 
AVERAGEX(
    Stores,
    [PY Sales]
)

YoY % Change (Avg) = 
DIVIDE(
    [Avg Sales per Store] - [Avg PY Sales per Store],
    [Avg PY Sales per Store]
)

Then you can put those measure in combo chart to get your required output.  

I have attached a pbix file for your reference.

Best regards,

[CC125]

Thanks for the detailed response. I've followed your above steps and the Weeks From Refurb is working correctly I believe. However, I'm not sure the PY Sales per Store measure is working as I expect it to. When I filter just to the stores who have had 17 weeks since refurb (the max length of time since refurb possible) the values for Avg PY Sales per Store and Avg Sales per Store don't change. I'd like to just show them by the stores that have refurbed that long ago (i.e stores that had a refurb 5 weeks ago shouldn't have any sales values included in the calculation at 17 weeks out from refurb).

I was also getting a syntax error on Stores (my value is called Store No) and changed it to the below, is that correct?

Avg PY Sales per Store =

AVERAGEX(

    VALUES(DailySales[Store No]),

    [PY Sales]

)

I would also ideally like to look at a flat average, at the moment it's based on sales and therefore a weighted average but I'd prefer to take all the stores YoY positions and then just average the result, so it's not influenced by stores with more sales