Forum Discussion
Problem creating a dates column
Hello everybody.
I'm new using Power Bi and I need help doing a simple task that is causing me problems.
I'll try to explain as best as I can. I have a big table with lots of columns but in this case i only need to use 2 columns. Column A is where the Order numbers are and column B has all the dates. Each line of the table is related to a task, so a simple Order number can be listed in a lot of lines and each of this tasks can be finished in a different date.
I want to create a column where for each Order Number i get the last date reported. The objective is to merge this new column with the Order number so i can get when was the Order Number finished (the day when the last task was finished) because for now the same Order Number is finished more than one time.
I try to give an example: (imagine there are other columns with tasks and lots of lines, its just to depict what i want to achieve)
COLUMN A COLUMN B NEW COLUMN
ON1 1/3/18 6/3/18
ON2 2/3/18 2/3/18
ON1 5/3/18 6/3/18
ON1 6/3/18 6/3/18
ON2 23/2/18 2/3/18
ON3 2/3/18 4/3/18
ON3 4/3/18 4/3/18
I tried to create New Column using CALCULATE, LASTDAY, FILTER and other functions but I'm not able to figure how to do it.
Can anybody lend me a hand?
Thanks in advance. This community is great!
Try this:
New Column = CALCULATE(MAX(Table1[ColumnB]), ALLEXCEPT(Table1,Table1[ColumnA]))
(LASTDATE instead of MAX should work just as well.)
The ALLEXCPET removes all filter context except for the column(s) specified. Since we want to find the max over matching values in ColumnA, that's the filter context we want to keep.
8 Replies
Try this:
New Column = CALCULATE(MAX(Table1[ColumnB]), ALLEXCEPT(Table1,Table1[ColumnA]))
(LASTDATE instead of MAX should work just as well.)
The ALLEXCPET removes all filter context except for the column(s) specified. Since we want to find the max over matching values in ColumnA, that's the filter context we want to keep.
Nice! IT WORKS!!!
I tried something similar before and failed but your formula worked perfectly!
Thanks a lot Alexis. I have done some nice things with power bi, but i'm new in this world and sometimes a simple task (or not so simple) requires me lots of time to resolve.
Thanks! very appreciated!
Please mark it as a solution if it solved your problem and award kudos (thumbs up) if you feel like it.
Hi,
Try this calculated column formula
=CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Order Number]=EARLIER(Data[Order Number])))
Hope this helps.
Hi,
Can I add a filter to the above formulas? Both work, but the problem I have now is that when a task is "not completed" it gives me a date if some other tasks are "completed" in the same order. The program i use to get the data gives me a column with current state: finished "yes" or "no" so I only need to add some Filter to check this column, but everything i have tried isn't working. My goal is to get the "end date" only when the task is completed (obviously a "yes") and get a blank space if it isn't.
On the same subject, to get my "ultimate goal", i need to concatenate the "plate number" with the "end date" (to use distinctcount and get the number of vehicles finished) but with "Concatenate" I'm getting results like "2343HDS17/12/2018" (correct) and "2343HDS" (incorrect, because it concatenates even when there are blank spaces). Could somebody suggest some way to use concatenate only when column A and B have not "blank spaces"?
Sorry for being so tiresome but I spend lots of time with Power bi and I love the program a lot, but is driving me crazy...and I'm new and any task is a big mountain to climb (and the boss always have some devilish ideas to "improve" the work).
Thanks a lot for your help, step by step i'm advancing thanks of you...
Hi,
Share some data and show the expected result.
