Forum Discussion

abyars's avatar
abyars
Frequent Visitor
10 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
    10 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 🙌

Aala_Ali's avatar
Aala_Ali
Most Valuable Professional
10 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 🙌

abyars's avatar
abyars
Frequent Visitor
10 months ago

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.