Calendar

[tmhalila]

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

Year	Quarter	Value
2019	FY19Q1	23
2019	FY19Q2	50
2019	FY19Q3	98
2019	FY19Q4	102
2020	FY20Q1	35
2020	FY20Q2	454
2020	FY20Q3	66
2020	FY20Q4	757
2021	FY21Q1	330
2021	FY21Q2	11
2021	FY21Q3	236
2021	FY21Q4	45

[PaulDBrown · Accepted solution]

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]

Thank you so much, Paul!

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

[kumarthombre20]

@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]

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

[amitchandak]

@tmhalila , refer by blog for DAX

.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

Video DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s

Power query

https://www.goodly.co.in/custom-fiscal-year-quarter-power-query/

[tmhalila]

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?

[tmhalila]

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.