Problem creating a dates column

[Sorakell]

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!

[Ashish_Mathur]

Hi,

Try this calculated column formula

=CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Order Number]=EARLIER(Data[Order Number])))

Hope this helps.

[Sorakell]

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

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Sorakell]

testing

I think its that.! IN my orignal .pbix I have more tables and I have filtered many columns. But all the data I need for the calculation is here. 

My objectives are:

1* To not count non working days when I want to make a calculus with pasts days. The 2 measures where i need that are ...of yesterday and past 7 days. For example on monday i need it to calculate Saturday (because my company works on saturdays).

2* In the CONCATENATE formula I need it to concatenate only if finished column displays "yes" and it has an "end date". I get weird results if I don't do it this way.

3* For the above point I need also that the column of "end date" only gives me an end date if the finished status is yes, if not i need it to give me a blank space.

I'll try to explain a little more. I work in a company that has around 500 vehicles and we are making an study of the vehicles that enter the workshop every day (more exactly the vehicles that have been repaired every day). With the .pbix i have now we got weird results because some unfinished tasks are counted (3*). The first step i asked in above posts was for finding a way to filter vehicles per day because any vehicle has multiple tasks i needed a way to filter for obtaining only one end date (because otherwise the same vehicle could be finished multiple days). But I'm still getting weird results because the formula doesn't filter the non finished tasks (2*)

Sorry if I'm not explining well, but I'm not native in english language and explaining complex things like that isn't easy,

Anyway, thanks for your help and your interest. I'm sure sooner or later I will get the objective, because all the tips i get and the posts i read are helping me a lot.

[Sorakell]

Sorry. I don't know why but it seems the link isn't working.

I try to resume the process step by step posting pics.

end date OT = CALCULATE(MIN(Tasks[Finalizado- Fecha]);ALLEXCEPT('Tasks';Tasks[Nº Orden trabajo]))

Concatenado matricula-fin = CONCATENATE('Tasks'[Nº Vehículo];'Tasks'[end date OT])

The above formulas are what i have currently. As you can see End date gives me a date even if the task isn't finished. I need it to give me a date only when finished is "si".If its a"no" i want a blank space.

Then I need the concatenate only when I have a value in "End date! and in "nº vehiculo". If I don't have a value in "both" columns i would like to have a blank space.

Then i have some other measures to get the vehicles the company finishes every day. Those meaures are:

Cantidad de vehiculos = DISTINCTCOUNT(Tasks[Concatenado matricula-fin]) 

Cantidad de vehiculos TODAY =CALCULATE([Cantidad de vehiculos]; 'Calendar'[Date]=TODAY())

Cantidad de vehiculos PREVIOUS DAY = CALCULATE([Cantidad de vehiculos];DATEADD('Calendar'[Date]=TODAY();-1;DAY)

Vehiculos LAST 7 DAYS = CALCULATE([Cantidad de vehiculos];DATESINPERIOD('Calendar'[Date];TODAY();-7;DAY);'Auxiliar calendar'[es laborable?]="si")

 I have this auxiliary Calendar. As you can see the "sundays" are non working days, and in this pics days 6 and 8 of december are "non working" days in Spain. The saturdays are "working days" always except when friday is a non working day (but i did the calendar in excel and this is reflected on the table). I need the previous formulas to take that in consideration and filter only Previous day and last 7 days of "working" days. and trying to add 'Auxiliar calendar'[es laborable?]="si") in the above formula doesn't work.

I hope somebody can help with this. And sorry because the data are in spanish. If someone needs some translation i'll try to explain as better as i can.

Please. This is the last things i need for this project...and then i would jump to the "harder ones" and even that scares me a little i think that once this current one its completed the other ones won't be so hard as they seem right now...

Thanks a lot for your help and your patience. It will take me ages to do it if not for your help...

[AlexisOlson · Accepted solution]

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.

[Sorakell]

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!

[AlexisOlson]

Please mark it as a solution if it solved your problem and award kudos (thumbs up) if you feel like it.