Forum Discussion
How to create calculated column that identifies date census and returns month values
hello dcheng029
please check if this accomodate your need.
create a new custom column in PQ with following code
= Table.AddColumn(#"Changed Type", "Outstanding PQ", each if [Date Received]=[Date Closed] then "" else if Date.EndOfMonth([Date Received])=Date.EndOfMonth(Date.AddMonths([Date Closed],-1)) then Date.ToText([Date Received],"MMM yy") else Text.Combine({Date.ToText([Date Received],"MMM yy"),Date.ToText(Date.EndOfMonth(Date.AddMonths([Date Closed],-1)),"MMM yy")},", "))also as you and lbendlin said, "Split Column" only works in PQ.
But if you have to work in DAX, you can tweak your DAX to extract a certain value using DAX such as LEFT/MID/RIGHT, PATHITEM, and i am sure there are more.
Hope this will help.
Thank you.
hello dcheng029
please check if this accomodate your need.
create a new custom column in PQ with following code
= Table.AddColumn(#"Changed Type", "Outstanding PQ", each if [Date Received]=[Date Closed] then "" else if Date.EndOfMonth([Date Received])=Date.EndOfMonth(Date.AddMonths([Date Closed],-1)) then Date.ToText([Date Received],"MMM yy") else Text.Combine({Date.ToText([Date Received],"MMM yy"),Date.ToText(Date.EndOfMonth(Date.AddMonths([Date Closed],-1)),"MMM yy")},", "))
also as you and lbendlin said, "Split Column" only works in PQ.
But if you have to work in DAX, you can tweak your DAX to extract a certain value using DAX such as LEFT/MID/RIGHT, PATHITEM, and i am sure there are more.
Hope this will help.
Thank you.
Hi Irwan, sorry to revisit this one but I have noticed that if the date received and date closed is further than 2 months apart, the Custom Column does not return all necessary month values.
E.g. when I change the Date Received to 01/12/2023 on ID 100, I am expecting the Outstanding PQ to return "Dec 23, Jan 24, Feb 24" but instead it only returns "Dec 23, Feb 24". How would I get it to return all months where the EOM date is within the Date Received and Date Closed?
hello dcheng029
to be honest, your need seems much easier and straightforward in DAX with lbendlin 's solution because DAX has date value from calendar.
but if you really want to do in PQ, then try this code.
to get previous or next value might be easy but when you want to breakdown the month inside [Date Received] and [Date Closed] is quite tricky.
PQ does not have calendar as DAX so you need to recreate date in between then transform many times to achive your result (not sure if there is any easier code).
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc5RCgAhCATQu/QdlJZtnSW6/zWW1mQq2D/xjTK9O4rReUeBOHD6phQ4u+EnkdJczIEhjOh1o0+yfROIRovdVIicUiAF0Tk0yKPdohFt7ar1FgwqzeqK1VxC8ZQEoV3WjnF+/Um7rF3+TcuZHi8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Date Received" = _t, #"Date Closed" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date Received", Date.Type}, {"Date Closed", Date.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Date Closed]<>[Date Received] and [Date Closed]<>null then {Number.From([Date Received])..Number.From(Date.AddMonths([Date Closed],-1))} else if [Date Closed]=[Date Received] then {Number.From([Date Received])..Number.From(Date.AddMonths([Date Closed],-1))} else null),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Changed Type2" = Table.ReplaceValue(#"Changed Type1", each [Custom], each if [Custom]=null then [Date Received] else [Custom], Replacer.ReplaceValue,{"Custom"}),
#"Calculated End of Month" = Table.TransformColumns(#"Changed Type2",{{"Custom", Date.EndOfMonth, type date}}),
#"Filtered Rows" = Table.SelectRows(#"Calculated End of Month", each ([Custom] <> null)),
#"Changed Type3" = Table.TransformColumns(#"Filtered Rows",{"Custom", each Date.ToText(_,"MMM-yy"), type text}),
#"Grouped Rows" = Table.Group(#"Changed Type3", {"ID", "Date Received", "Date Closed", "Custom"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Count"}),
#"Grouped Row" = Table.Group(#"Removed Columns", {"ID", "Date Received", "Date Closed"}, {{"Outstanding", each Text.Combine([Custom], ", "), type text}}),
#"Change Type4" = Table.ReplaceValue(#"Grouped Row", each [Outstanding], each if [Date Closed]=[Date Received] then null else [Outstanding], Replacer.ReplaceValue,{"Outstanding"})
in
#"Change Type4"Hope this will help.
Thank you.