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
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-Calendar-1-5-Power/ba-p/1187441
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?