Transforming Data

[Boycie92]

Hi,

I am looking for some advice.

I want to be able to transform data that looks like this:

Building	Date	Task	Status
Building 1	Lock Doors	01/01/2020	Done
Building 1	Hover	01/02/2020	Not Done
Building 2	Empty bins	02/01/2020	Nor Done

Into this:

Building	Task	Previous Date	Previous Status	Current Date	Current Status
Building 1	Lock Doors	01/01/2020	Done	06/04/2020	Done
Building 1	Hover	01/02/2020	Not Done	07/04/2020	Not Done
Building 2	Empty bins	02/01/2020	Nor Done	08/04/2020	Done

Essentially, I have a number of buildings. In each building there are various task to do. I want to be able to see the Current and Previous Status (using the Date) of a task, to see if it was done or not.

Does anyone have any suggestions on how to get it into this format using either Power Query or DAX?

Thanks,

Boycie92

[v-luwang-msft]

Hi @Boycie92 ，

Can you provide the specific formula for calculating Current Date and Current Status?

Best Regards

Lucien

[Boycie92]

HI @v-luwang-msft 

I don't have any. I was hoping the community could help with that? and the previous formulas?

[amitchandak]

@Boycie92 , Your data is not relating to the first table.

Usually in dax new column , you can get previous status and date like

previous date = maxx(filter(Table, [Building] =earlier([Building]) && [Date] <earlier([date])),[Date])

previous Status= maxx(filter(Table, [Building] =earlier([Building]) && [Date] = earlier([previous date])),[Status])

[Anonymous]

Hi,

I don't see where you would get the current dates from in the upper table. Can you not add them in your data source and then import it to Excel? Without any consistent pattern in those dates I don't think PBI is the right tool to add another column to your dataset.

Regards,

Jasper

[Boycie92]

Hi @Anonymous 

That is part of the problem I would need (somehow) to be able to idenity the current date based on the last date entry for a sepcific task that relates to a building.

Thanks,