Forum Discussion
How to create calculated column that identifies date census and returns month values
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.
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.
11 Replies
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],", ")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.
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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
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.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.
