cancel
Showing results for
Search instead for
Did you mean:
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

 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

1 ACCEPTED SOLUTION
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 )
``````

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

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.

7 REPLIES 7
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 )
``````

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

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.

Advocate I

Thank you so much, Paul!

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

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``````

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

Advocate I

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

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 !!
Advocate I

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?

Advocate I

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

#### The Power BI Community Show

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

#### Ted's Dev Camp - July 28, 2022

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

#### 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