Forum Discussion

Boycie92's avatar
Boycie92
Resolver I
5 years ago

Transforming Data

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

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Boycie92
      Resolver I
      5 years ago

      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,

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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])

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    Hi Boycie92 ，

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

     

    Best Regards

    Lucien

    • Boycie92's avatar
      Boycie92
      Resolver I
      5 years ago

      HI v-luwang-msft 

       

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