Forum Discussion
Calendar
- 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" & FQAdd 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 FYPeriodAnd 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
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
- tmhalila4 years agoResolver II
Thank you so much, Paul!
I appreciate your in-depth knowledge. This is what I wanted you saved my week 🙏.