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 calculated column with following DAX.
Outstanding =
IF(
'Table'[Date Received]='Table'[Date Closed],
"",
IF(
EOMONTH('Table'[Date Received],0)=EOMONTH('Table'[Date Closed],-1),
FORMAT('Table'[Date Received],"MMM YY"),
FORMAT('Table'[Date Received],"MMM YY")&", "&FORMAT(EOMONTH('Table'[Date Closed],-1),"MMM YY")
))
Thank you for the breakdown and the .pbix file, appreciate it.
I am hoping to split the Outstanding column by the column delimiter so I can graph the outstanding month and case numbers together; I understand this can only be done on Power Query. Would you have any pointers on how to achieve the same output but on Power Query so I can split the column?
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.
