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.
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/1447523Post 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.