cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tmhalila
Advocate I
Advocate I

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

 

1 ACCEPTED SOLUTION
PaulDBrown
Super User
Super User

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 )

 

 

StartDate.jpg

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

 

 

 

Calendar.jpg

 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.

 

FY.jpg

 

 Create the relationship between the date fields:
2022-03-29.png

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

7 REPLIES 7
PaulDBrown
Super User
Super User

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 )

 

 

StartDate.jpg

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

 

 

 

Calendar.jpg

 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.

 

FY.jpg

 

 Create the relationship between the date fields:
2022-03-29.png

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Thank you so much, Paul!

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

kumarthombre20
Resolver I
Resolver I

@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

 

kumarthombre20_0-1648489991302.png

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

 

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
Super User
Super User

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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

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?

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.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors