Forum Discussion
Helper IConversing DAX to custom column Query Setting
Hello - calculate how many holidays employee should taken since offboarding date
I twas trying figure out how to do this in the power query.
Holiday_days = var _start_date = IF( [OnBoardText] > MIN('Calendar'[Date]) ,[OnBoardText], MIN('Calendar'[Date]))
var _t= FILTER('Calendar','Calendar'[Date] >= _start_date && 'Calendar'[Date] <= [OffBoardText])
return
SUMX(_t,[is_holiday])
Calendar [Date] is another table.. while the onboard is within the same table
Hi annie_liu ,
My approach was a bit complicated, but I made it. You can refer to the following M code. You can also download my attachment to see details.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLUtdA1MoawjXQNdY0MlWJ1opWMkERgTCDHDCwJUg4TNAXriAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, OnBoardText = _t, OffBoardText = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"OnBoardText", type date}, {"OffBoardText", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Min(#"Table (2)","Date")), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Date"}, {"Custom.Date"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom.Date", type date}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "MinDate", each if [OnBoardText] = null then [Custom.Date] else if [Custom.Date] < [OnBoardText] then [OnBoardText] else [Custom.Date]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom.Date"}), #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each #"Table"), #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"Date", "Holiday Count"}, {"Date", "Holiday Count"}), #"Added Custom3" = Table.AddColumn(#"Expanded Custom1", "Custom", each if [MinDate]<=[Date] and [OffBoardText]>=[Date] then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom3", each ([Custom] = 1)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}), #"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each ([Holiday Count] = 1)), #"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Id"}, {{"All Rows", each _, type table [Id=nullable number, OnBoardText=nullable date, OffBoardText=nullable date, MinDate=date, Date=date, Holiday Count=number]}, {"Holiday Count", each Table.RowCount(_), Int64.Type}}), #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Id", "OnBoardText", "OffBoardText"}, {"Id.1", "OnBoardText", "OffBoardText"}) in #"Expanded All Rows"Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi annie_liu ,
My approach was a bit complicated, but I made it. You can refer to the following M code. You can also download my attachment to see details.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLUtdA1MoawjXQNdY0MlWJ1opWMkERgTCDHDCwJUg4TNAXriAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, OnBoardText = _t, OffBoardText = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"OnBoardText", type date}, {"OffBoardText", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Min(#"Table (2)","Date")), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Date"}, {"Custom.Date"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom.Date", type date}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "MinDate", each if [OnBoardText] = null then [Custom.Date] else if [Custom.Date] < [OnBoardText] then [OnBoardText] else [Custom.Date]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom.Date"}), #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each #"Table"), #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"Date", "Holiday Count"}, {"Date", "Holiday Count"}), #"Added Custom3" = Table.AddColumn(#"Expanded Custom1", "Custom", each if [MinDate]<=[Date] and [OffBoardText]>=[Date] then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom3", each ([Custom] = 1)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}), #"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each ([Holiday Count] = 1)), #"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Id"}, {{"All Rows", each _, type table [Id=nullable number, OnBoardText=nullable date, OffBoardText=nullable date, MinDate=date, Date=date, Holiday Count=number]}, {"Holiday Count", each Table.RowCount(_), Int64.Type}}), #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Id", "OnBoardText", "OffBoardText"}, {"Id.1", "OnBoardText", "OffBoardText"}) in #"Expanded All Rows"Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.