Creating a calculated column that returns the first 52 weeks a store is open

[abyars]

See example below:

I am trying to figure out a way to create a matrix that gathers the first 52 weeks a store is open to monitor performance. Each store has a different opening date, but I'd like to find a way to join them all together by their 1st, 2nd, 3rd, etc. week open. I have found no success using calculated columns. Any help would be greatly appreciated.

[Aala_Ali]

You can’t drop a measure directly in the Columns well. Create a tiny disconnected table GENERATESERIES(1,52,1) for the headers, then write a measure that maps each header to a 7-day window after the store’s open date.

Because your Date and OpenDate are in different tables with no relationship, use TREATAS() inside the measure to create virtual relationships at query time—no model changes needed:

Sales (Aligned First 52 Weeks) :=

VAR k = SELECTEDVALUE ( WeeksSinceOpen[Value] )

VAR OpenD = [Open Date]

VAR WeekStart = OpenD + (k - 1) * 7

VAR WeekEnd   = WeekStart + 6

RETURN

CALCULATE(

  [Sales],

  TREATAS( DATESBETWEEN('Date'[Date], WeekStart, WeekEnd), Fact[Date] ),

  TREATAS( VALUES(Store[StoreID]), Fact[StoreID] )   -- if Store isn’t related to Fact

)

Columns = WeeksSinceOpen[Value], Rows = Store, Values = the measure above.

If you find this helpful give a Kudos 🙌

[srlabhe]

Create a Weeks Since Open column

First, create a calculated column in your Sales fact table to determine the number of weeks that have passed since each store's opening. This column will be your new axis for analysis.

1. In Power BI Desktop, navigate to the Data view.
2. Select your Sales table in the Fields pane.
3. Click New column in the ribbon.
4. Enter the following DAX formula:
   dax

   Weeks Since Open = 
   VAR CurrentSalesDate = 'Sales'[SalesDate]
   VAR StoreOpeningDate = 
       LOOKUPVALUE(
           'Stores'[StoreOpenDate], 
           'Stores'[StoreID], 
           'Sales'[StoreID]
       )
   RETURN
       IF(
           CurrentSalesDate >= StoreOpeningDate,
           INT(DATEDIFF(StoreOpeningDate, CurrentSalesDate, WEEK)) + 1,
           BLANK()
       )

   • LOOKUPVALUE: This function retrieves the correct StoreOpenDate from the Stores table for each corresponding StoreID in your Sales table.
   • DATEDIFF: This calculates the difference in weeks between the store's open date and the sales date.
   • + 1: The DATEDIFF function starts counting from zero, so adding one makes the first week a more intuitive 1.
   • IF: This ensures that only transactions that occurred after the store opened are counted. 

Step 2: Build the matrix visual

With the Weeks Since Open column created, you can now build your matrix visual to compare store performance.

1. Select the Matrix visual from the Visualizations pane.
2. Add Weeks Since Open to the Columns field.
3. Add StoreID (or StoreName) to the Rows field.
4. Add your desired measure, such as Total Sales, to the Values field. 

Your matrix will now display each store's performance aligned by the week number since its opening, allowing for a like-for-like comparison of their first 52 weeks. 

Step 3: Filter for the first 52 weeks

To limit your analysis to the first 52 weeks, you can apply a visual-level filter.

1. With the matrix visual selected, find the Filters pane.
2. Expand the Weeks Since Open filter.
3. Change the filter type to is less than or equal to.
4. Enter 52 in the value box. 

[Aala_Ali · Accepted solution]

Hi @abyars 

You can align all stores by “age since opening” with either a quick calculated column or a lightweight measure—no complex time-intelligence needed.

Option A (simplest): add WeekSinceOpen_1_52 on your fact:

WeekSinceOpen_1_52 :=

VAR OpenD = RELATED(Store[OpenDate])

VAR DaysFromOpen = DATEDIFF(OpenD, Fact[Date], DAY)

VAR WeekIdx = QUOTIENT(DaysFromOpen, 7) + 1

RETURN IF(DaysFromOpen >= 0 && WeekIdx <= 52, WeekIdx)

Then build a matrix: Rows = Store, Columns = WeekSinceOpen_1_52, Values = your KPI.

Option B (no new column):
create a helper table GENERATESERIES(1,52,1) and a measure that maps each week index back to real dates using the store’s min date (opening), with ALLEXCEPT to keep the store context.
This aligns Week 1..52 for every store regardless of calendar date.

If it helps, please hit Accept as Solution & give a Kudos so others can find it faster 🙌

[abyars]

Could you elaborate on how to turn the Column into a measure? I've tried, but the it won't allow me to use the measure in the Columns field.

Another problem I have is that my Date column and OpenDate columns are on 2 separate tables with no way to be directly related due to the complexity of the model.