Forum Discussion

abyars's avatar
abyars
Frequent Visitor
11 months ago
Solved

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

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 l...
Need Help
Tips and Tricks
  • Aala_Ali's avatar
    Aala_Ali
    11 months ago

    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 🙌

srlabhe's avatar
srlabhe
Icon for Super User rankSuper User
11 months ago
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.