Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
Proud to be a Super User!
Paul on Linkedin.
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
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 🙏.
@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
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
@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/
Hello amitchandak,
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |