Forum Discussion

tmhalila's avatar
tmhalila
Icon for Resolver II rankResolver II
4 years ago
Solved

Calendar

Hi,

Someone assist how I can create a calendar table using DAX or Power Query and connect to the fact table which is missing the date column. The Fact table only contains a Year and Quater Column but I know exactly when are the quarter start and the end date like October 1 to Sept 30.

 

I need the calendar table to support the calculation of the previous quarter's performance compared to the current performance

Sample Fact table

YearQuarterValue
2019FY19Q123
2019FY19Q250
2019FY19Q398
2019FY19Q4102
2020FY20Q135
2020FY20Q2454
2020FY20Q366
2020FY20Q4757
2021FY21Q1330
2021FY21Q211
2021FY21Q3236
2021FY21Q445

 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    Edited to add a couple of columns which are probably needed

    See if this works. I'm assuming that FY19 begins on the 01/10/2019 and ends on the 30/09/2020.
    Create a new column in your fact table using:

     

    Quarter Start Date =
VAR QT =
    VALUE ( RIGHT ( 'Table'[Quarter], 1 ) )
VAR MNTH =
    SWITCH ( QT, 1, 10, 2, 1, 3, 4, 4, 7 )
VAR FY =
    IF ( MNTH = 10, 'Table'[Year], 'Table'[Year] + 1 )
RETURN
    DATE ( FY, MNTH, 1 )

     

     

     Now create the calendar table using:

     

    Calendar Table =
ADDCOLUMNS (
    CALENDAR (
        MIN ( 'Table'[Quarter Start Date] ),
        MAX ( 'Table'[Quarter Start Date] )
    ),
    "MonthNum", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Year", YEAR ( [Date] )
)

     

     

     

     Add a column to the calendar table for the FYQ using:

     

    FYQ =
VAR FY =
    IF (
        'Calendar Table'[MonthNum] < 10,
        'Calendar Table'[Year] - 1,
        'Calendar Table'[Year]
    )
VAR FQ =
    SWITCH (
        TRUE (),
        'Calendar Table'[MonthNum] > 9, "Q1",
        'Calendar Table'[MonthNum] < 4, "Q2",
        'Calendar Table'[MonthNum] < 7, "Q3",
        "Q4"
    )
RETURN
    FY & "FY" & FQ

     

    Add a FY Quarter period using:

     

    FY Qarter Period =
VAR FQ =
    SWITCH (
        TRUE (),
        'Calendar Table'[MonthNum] > 9, 1,
        'Calendar Table'[MonthNum] < 4, 2,
        'Calendar Table'[MonthNum] < 7, 3,
        4
    )
VAR FYPeriod = 'Calendar Table'[Year] * 100 + FQ
RETURN
    FYPeriod

     

    And a order column for the fiscal year to use in measures to reference other periods.

     

    Order = 
RANKX('Calendar Table', 'Calendar Table'[FY Qarter Period], , ASC, Dense)

    Add a FY Number:

    FYNumber = 
IF('Calendar Table'[MonthNum] < 10, 'Calendar Table'[Year] -1, 'Calendar Table'[Year])

    and FY Column

    FY = 
"FY" & RIGHT('Calendar Table'[FYNumber], 2)

    The FYQ and FY can now be sorted by their respective numeric columns to be used in visuals.

     

     

     Create the relationship between the date fields:

     

    Now you can create measures along the lines of:

     

     

    Sum Sales Previous Quarter =
CALCULATE (
    [Sum Sales],
    FILTER ( ALL ( Calendar ), Calendar[Order] = MAX ( Calendar[Order] ) - 1 )
)

     

     

     

    I've attached the sample PBIX file

7 Replies

    • tmhalila's avatar
      tmhalila
      Icon for Resolver II rankResolver II
      4 years ago

      Thank you for very useful tips Brother,

      But, sorry I think my question was not clean. Let me put it this way.

      How can I connect a Calendar table with a Fact Table? When a Fact Table does not have a Date Column, it only has a Year and Quarter.

       

      The sample above is the Fact Table that I need to connect with a Calendar.

    • tmhalila's avatar
      tmhalila
      Icon for Resolver II rankResolver II
      4 years ago

      Hello amitchandak,

      https://youtu.be/cJqgphIHXz8

      What you did here is the same as what I have in my Dataset Only Quarter and Year. Now is it acceptable to use the start date you created for connection with a Calendar DATE?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    tmhalila could you please try the below dax code

     

    dCalendar = 
VAr vMinYear = MIN('Table'[Year])
VAR vMaxYear = MAX('Table'[Year])
VAR Result = 
ADDCOLUMNS(
    CALENDAR(
            DATE(vMinYear,1,1),
            DATE(vMaxYear,12,31)
    ),
    "MonthNumber",MONTH([Date])
)
RETURN
Result

     

    Based on the Month column you can generate the Financial Quarter column

     

    • tmhalila's avatar
      tmhalila
      Icon for Resolver II rankResolver II
      4 years ago

      Thanks,

      This is on the way to solving the problem, now in which Column of the Fact Table will act like a Foreign Key by connecting it with the date column from the Calendar Table.

       

      Is there a means we can add two Calculated Column in Fact Table with Quarter start Date and Quarter-end date then we can use those dates to define our variable like 

      VAr vMinYear = MIN('Table'[quarterstartdate])
VAR vMaxYear = MAX('Table'[quarterenddate])

       

      Then, one of the columns let's say a quarterstartdate be used as a foreign key to connect with date column from the CALENDAR table

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Edited to add a couple of columns which are probably needed

    See if this works. I'm assuming that FY19 begins on the 01/10/2019 and ends on the 30/09/2020.
    Create a new column in your fact table using:

     

    Quarter Start Date =
VAR QT =
    VALUE ( RIGHT ( 'Table'[Quarter], 1 ) )
VAR MNTH =
    SWITCH ( QT, 1, 10, 2, 1, 3, 4, 4, 7 )
VAR FY =
    IF ( MNTH = 10, 'Table'[Year], 'Table'[Year] + 1 )
RETURN
    DATE ( FY, MNTH, 1 )

     

     

     Now create the calendar table using:

     

    Calendar Table =
ADDCOLUMNS (
    CALENDAR (
        MIN ( 'Table'[Quarter Start Date] ),
        MAX ( 'Table'[Quarter Start Date] )
    ),
    "MonthNum", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Year", YEAR ( [Date] )
)

     

     

     

     Add a column to the calendar table for the FYQ using:

     

    FYQ =
VAR FY =
    IF (
        'Calendar Table'[MonthNum] < 10,
        'Calendar Table'[Year] - 1,
        'Calendar Table'[Year]
    )
VAR FQ =
    SWITCH (
        TRUE (),
        'Calendar Table'[MonthNum] > 9, "Q1",
        'Calendar Table'[MonthNum] < 4, "Q2",
        'Calendar Table'[MonthNum] < 7, "Q3",
        "Q4"
    )
RETURN
    FY & "FY" & FQ

     

    Add a FY Quarter period using:

     

    FY Qarter Period =
VAR FQ =
    SWITCH (
        TRUE (),
        'Calendar Table'[MonthNum] > 9, 1,
        'Calendar Table'[MonthNum] < 4, 2,
        'Calendar Table'[MonthNum] < 7, 3,
        4
    )
VAR FYPeriod = 'Calendar Table'[Year] * 100 + FQ
RETURN
    FYPeriod

     

    And a order column for the fiscal year to use in measures to reference other periods.

     

    Order = 
RANKX('Calendar Table', 'Calendar Table'[FY Qarter Period], , ASC, Dense)

    Add a FY Number:

    FYNumber = 
IF('Calendar Table'[MonthNum] < 10, 'Calendar Table'[Year] -1, 'Calendar Table'[Year])

    and FY Column

    FY = 
"FY" & RIGHT('Calendar Table'[FYNumber], 2)

    The FYQ and FY can now be sorted by their respective numeric columns to be used in visuals.

     

     

     Create the relationship between the date fields:

     

    Now you can create measures along the lines of:

     

     

    Sum Sales Previous Quarter =
CALCULATE (
    [Sum Sales],
    FILTER ( ALL ( Calendar ), Calendar[Order] = MAX ( Calendar[Order] ) - 1 )
)

     

     

     

    I've attached the sample PBIX file

    • tmhalila's avatar
      tmhalila
      Icon for Resolver II rankResolver II
      4 years ago

      Thank you so much, Paul!

      I appreciate your in-depth knowledge. This is what I wanted you saved my week 🙏.