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 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.