How to create calculated column that identifies date census and returns month values

[dcheng029]

Hi everyone,

I am working on a dataset which shows a list of job case ID and their respective date received and date closed (formatted as MM/YYYY).

I am wanting to create a custom column where Power BI will calculate if the ID is open at the end of each month, it will tag it as outstanding with the corresponding month (MMMM YY) name. 

ID	Date Received	Date Closed	Outstanding
100	01/2024	03/2024	Jan 24, Feb 24
101	01/2024	02/2024	Jan 24
102	03/2024	03/2024
103	04/2024	05/2024	Apr 24
104	06/2024	08/2024	Jun 24, Jul 24
105	06/2024	06/2024
106	08/2024	09/2024	Aug 24
107	10/2024	12/2024	Oct 24, Nov 24
108	01/2025	01/2025
109	03/2025	04/2025	Mar 25
110	03/2025	03/2025
111	03/2025		Mar 25
112	04/2025	04/2025
113	04/2025		Apr 25
114	04/2025	04/2025
115	04/2025		Apr 25

e.g. ID 100 was open in January 2024 and closed in March 2024, so as at 31/01/2024 and 29/02/2024 the job case was still open and outstanding, so the 'Outstanding' column should show the "Jan 24, Feb 24." Job cases open and closed within the same month won't have any values in the 'Outstanding' column as they would not have been open at the end of the month. Job cases open but not yet closed will follow the end of month census date (i.e. ID 111 was open in March 2025 but still open, so as at 31/03/2025 it has the Mar 25 outstanding tag. Same logic goes for ID 113 and 115 if they remain open at 31/04/2025).

Can someone please point me in the right direction of how to achieve this custom column on Power Query (as I then require to split this column by the comma delimiter to create graphs and visuals)? Thanks in advance.

[lbendlin]

Outstanding = 
var c =ADDCOLUMNS(CALENDAR([Date Received],COALESCE([Date Closed],TODAY())),"m",FORMAT([Date],"MMM YY"))
return CONCATENATEX(GROUPBY(FILTER(c,FORMAT(COALESCE([Date Closed],TODAY()),"MMM YY")<>[m]),[m]),[m],", ")

[dcheng029]

That's awesome, thank you for the pointers!

If I wanted to split the Outstanding column by the comma delimiter, how would I achieve this? 

I would like to create a column graph with the month values and count of ID cases.

Thanks again.

[lbendlin]

That is a very, very different requirement. You cannot "split columns"  in DAX. That only works in Power Query.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[dcheng029]

Post edited to be a table instead of a screenshot; desired outcome remains unchanged, I require the Outstanding column in Power Query so I can split by the column delimiter.

[Irwan]

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")
))

Also i am not sure ID111 has Mar25, but ID113 and ID115 have no value. All those three have blank Date Closed.

Hope this will help.

Thank you.

[dcheng029]

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?

[Irwan · Accepted solution]

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.

[dcheng029]

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?

[Irwan]

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.

[dcheng029]

Hi @Irwan thank you so much for the quick help, very much appreciated!

[Irwan]

hello @dcheng029 

glad to be a help.

also please Accept as Solution if any of these posts help you so others can find it easily.

Thank you.