Hi All,
I need help composing a DAX formula to add a custom quarter calculated column to my Dates table. The logic is that the quarter end is always the 3rd Friday of the last month of the quarter. See sample data below. Also attached excel file. Q4-2018 is 9/22/18 - 12/21/18 and Q1-2019 is 12/22/19 - 3/15/19.Custom Quarter ending on 3rd Friday.xlsx (37.8 KB)
Thank you,
Ferdinand
My approach consisted of starting from the last Friday of the previous month and work my way to the 3rd Friday of the current month.
Using @sam.mckay 's standard date table and field names, let’s get started and work through the process.
Find the date previous month:
LM Date =
DATEADD(Dates[Date],-1, MONTH)
Find the 3rd Friday of the current month
3rd Friday CutOff Date =
ENDOFMONTH(Dates[LM Date])+1- WEEKDAY( ENDOFMONTH( Dates[LM Date]) +1 - 6)+21
Determination the Month Number/Quarter/Year based on 3rd Friday grouping:
3rd Friday Month =
IF( Dates[3rd Friday CutOff Date] < Dates[Date], mod(Dates[MonthOfYear],12)+1, Dates[MonthOfYear])
3rd Friday Quarter =
"Q"&INT( DIVIDE( Dates[3rd Friday Month] + 2, 3 ))
3rd Friday Year =
if( AND(Dates[3rd Friday Month]=1, Dates[3rd Friday CutOff Date] < Dates[Date]), YEAR( Dates[3rd Friday CutOff Date])+1, YEAR( Dates[3rd Friday CutOff Date]))
3rd Friday Year and Quarter =
Dates[3rd Friday Year]&Dates[3rd Friday Quarter]
The last part consist of showing the correct Month End Date of each row on 3rd Friday basis. So far, I find DAX quite strict when mixing logic IF and ENDOFMONTH in the same formula maybe that can be simplified with VAR, I would like to learn how but for now I have to create a new column showing the next cutoff date so I can select the applicable month end date during the month when the date is greater than the cutoff date for that month.
Determination of Month End Date for each row on 3rd Friday basis
3rd Friday Next Cut-Off =
ENDOFMONTH(Dates[Date])+1- WEEKDAY( ENDOFMONTH( Dates[Date]) +1 - 6)+21
3rd Friday Base Month End =
IF( Dates[3rd Friday CutOff Date] < Dates[Date], Dates[3rd Friday Next Cut-Off], Dates[3rd Friday CutOff Date])
So now we have everything we need to get started on the model which is based on 3rd Friday of the month. I am not excluding refinements and optimization at least we can get going now. Also I use straight forward language to define each step but that too can be optimized once all the formulas are set in place in the model.
Have a great day!!
Richard D.
Hi Richard,
Thank you for your solution to this and sorry for the late response. I actually also came up with a solution that leverages the existing columns in my date table. I was aiming for a DAX calculated column but it just is beyond me. My solution involves 2 additional query steps. Step 1 is adding a custom column with this formula:
if List.Contains({3, 6, 9, 12}, [Month Number]) = false then ""
else if [Day] < 15 or [Day] > 21
then ""
else if [WeekDay] = "Fri"
then [Date]
else ""
Step 2 is change type and fill-up.
I ended up with this. I will then just add a quarter format column of my T&M Quarter column. Let me know if you are able to find a DAX solution.
Best regards,
Ferdinand
Here’s the final result and the Date table full query.
let
EndDate = #date(2021, 12, 31),
StartDate = #date(2017, 1, 1),
Custom = List.Dates(Date.From(StartDate), Number.From(EndDate) - Number.From(StartDate) +1 ,#duration(1,0,0,0)),
#"Convert List to Table" = Table.FromList(Custom, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Change Type" = Table.TransformColumnTypes(#"Convert List to Table",{{"Column1", type date}}),
#"Rename Column" = Table.RenameColumns(#"Change Type",{{"Column1", "Date"}}),
Year = Table.AddColumn(#"Rename Column", "Year", each Date.Year([Date]), type number),
#"Month Number" = Table.AddColumn(Year, "Month Number", each Date.Month([Date]), type number),
#"Month Name" = Table.AddColumn(#"Month Number", "Month Name", each Text.Start(Date.MonthName([Date]),3), type text),
Day = Table.AddColumn(#"Month Name", "Day", each Date.Day([Date]), type number),
Qtr = Table.AddColumn(Day, "Qtr", each "Q" & Number.ToText(Date.QuarterOfYear([Date])),type text),
#"Year-Qtr" = Table.AddColumn(Qtr, "Year-Qtr", each Text.Combine({Text.From([Year], "en-US"), [Qtr]}, "-"), type text),
StartOfQuarter = Table.AddColumn(#"Year-Qtr", "StartOfQuarter", each Date.StartOfQuarter([Date]), type date),
EndOfQuarter = Table.AddColumn(StartOfQuarter, "EndOfQuarter", each Date.EndOfQuarter([Date]), type date),
EndOfMonth = Table.AddColumn(EndOfQuarter, "EndOfMonth", each Date.EndOfMonth([Date]), type date),
DayOfYear = Table.AddColumn(EndOfMonth, "DayOfYear", each Date.DayOfYear([Date]), type number),
WeekDay = Table.AddColumn(DayOfYear, "WeekDay", each Date.ToText([Date], "ddd"),type text),
#"Inserted Merged Column" = Table.AddColumn(WeekDay, "MonthYear", each Text.Combine({[Month Name], Text.End(Text.From([Year], "en-US"),2)}, "-"), type text),
#"Added Custom1" = Table.AddColumn(#"Inserted Merged Column", "T&M Qtr-Start", each if List.Contains({3, 6, 9, 12}, [Month Number]) = false then ""
else if [Day] < 16 or [Day] > 22
then ""
else if [WeekDay] = "Sat"
then [Date]
else ""),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "T&M Qtr-End", each if List.Contains({3, 6, 9, 12}, [Month Number]) = false then ""
else if [Day] < 15 or [Day] > 21
then ""
else if [WeekDay] = "Fri"
then [Date]
else ""),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"T&M Qtr-Start", type date}, {"T&M Qtr-End", type date}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"T&M Qtr-Start"}),
#"Filled Up" = Table.FillUp(#"Filled Down",{"T&M Qtr-End"}),
#"Inserted Year" = Table.AddColumn(#"Filled Up", "T&M Year", each Date.Year([#"T&M Qtr-End"])),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "T&M Qtr", each "Q" & Number.ToText(Date.QuarterOfYear([#"T&M Qtr-End"])),type text),
#"Inserted Merged Column1" = Table.AddColumn(#"Inserted Quarter", "T&M Year-Qtr", each Text.Combine({Text.From([#"T&M Year"], "en-US"), [#"T&M Qtr"]}, "-"), type text)
in
#"Inserted Merged Column1"
Best regards,
Ferdinand
Hi Ferdinand
Going back to this problem, I apologize for not providing a more efficient DAX solution. I introduced some variables to resolve the month ending date using one DAX expression.
This solution will work in Power BI with any date table.
Here we go. Let’s start with finding out the applicable Month End Date (3rd Friday) of each row in the date table:
T&M-ME =
VAR FTM = EOMONTH( Dates[Date], -1) +1 - WEEKDAY( EOMONTH( Dates[Date], -1) - 5 ) +21
VAR FNM = EOMONTH( Dates[Date], 0) + 1 - WEEKDAY( EOMONTH( Dates[Date], 0 ) -5 ) +21
RETURN
IF( Dates[Date].[Date] > FTM, FNM, FTM)
We may need the following to be resolved for month, quarter, year and year & quarter:
T&M-M = MONTH( [T&M-ME])
T&M-Q = “Q”&INT( DIVIDE( Dates[T&M-M] + 2, 3 ))
T&M-Y = YEAR( [T&M-ME] )![3rdFridayStartEndQuarters|341x133]
T&M-Y&Q = Dates[T&M-Y]&“-”&Dates[T&M-Q]
Please see below an example of the table generated:
This solution is more elegant than the one I provided earlier and it is also very practical since it can be introduced into any date table.
Have a great day!!!
BR
Richard