Forum Discussion

tmhalila's avatar
tmhalila
Resolver 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 bu...
  • PaulDBrown's avatar
    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