Trying to get Previous Status from Status column to compare with Current Status

[rohanmate]

Hi Guys,

I am trying to create a custom column or measure to show the previous status of the project besides current status,

Example:

Table Contains:

ID       Date      Project Name    Status

1     5/10/18         ABC                  G

2     5/11/18         XYZ                  A

3     5/11/18       MNO                 R

4      5/24/18       ABC                  A

5      5/24/18       XYZ                   G

6      5/25/18      MNO                R

Output Required:

Project Name     Previous Status         Current Status

    ABC                        G                                 A

    XYZ                        A                                 G

   MNO                      R                                  R

Please help me built measure/ custom column for the previous status column and current status column in such a way that it automatically calculates previous status and current status refreshing the database

I tried using this logic where suppose N is current status which calculates the latest date for the project status then, N-1 should try populating its previous record from the table. But I have failed. I would really appreciate if anyone can help me building above calculated columns.

Thank you in advance

[Ashish_Mathur]

Hi,

You may refer to my solution in this PBIX file.

[rohanmate]

Hi Ashish_Mathur,

Thank you for reverting back, Your screenshot seems kind of excatly What I am trying to achive. But unfortunatly I am unable to open your PBIX file. Can you please resend me our paste the logic.

Thank You in advance.

[v-qiuyu-msft · Accepted solution]

Hi @rohanmate,

You can create a calculated column below: 

Previous Status = var t=CALCULATE(MAX('Table1'[ID]),FILTER('Table1','Table1'[ID]<EARLIER(Table1[ID])&& Table1[Project Name]=EARLIER(Table1[Project Name])))
return
LOOKUPVALUE(Table1[Status],Table1[ID],t)

Best Regards,
Qiuyun Yu 

[rohanmate]

I got the previous Status working by your formula, but I cant see Current status logic or column in the pbix file attached. Please can you help me with getting current status (Where it should give the status by most latest date logged for project name.... Basically it should return only one value)

Thank you in advance,

Rohan

[Ashish_Mathur]

Hi,

My solution gives you the previous and current status.  Did you try that?

[rohanmate]

Hi Ashish_Mathur,

I am still not following your directions, Is there any way you could paste the current and previous formula you used in your file and paste it here ?

Thanks

Rohan

[Ashish_Mathur]

Hi,

Used these measures

Most recent date = MAX(Data[Date])

Previous date = CALCULATE(MAX(Data[Date]),DATESBETWEEN(Data[Date],MINX(ALL(Data[Date]),Data[Date]),[Most recent date]-1))

Current Status = LOOKUPVALUE(Data[Status],Data[Date],[Most recent date],Data[Project Name],VALUES(Data[Project Name]))

Previous status = LOOKUPVALUE(Data[Status],Data[Date],[Previous date],Data[Project Name],VALUES(Data[Project Name]))

Please also read my previous posts carefully.

[rohanmate]

Hi Ashish_Mathur,

I am getting below error "A table of multiple values was supplied where a single value was expected" when creating measure Current Status = LOOKUPVALUE(Data[Status],Data[Date],[Most recent date],Data[Project Name],VALUES(Data[Project Name]))

Does this error has to deal with table relationships?

Any suggestiuons?

Thank You,

Rohan

[Ashish_Mathur]

Hi,

No, nothing to do with relatiosnhips.  Have you done what i suggested on 7/6/2018

You have to click on the visual and go to the Roller bruch icon in the Visualisation pane.  Remov the Totals from there.  Now drag the measure in.

[rohanmate]

It Also gives me error stating "A table of multiple values was supplied where a single value was expected."

Thank You.

[Ashish_Mathur]

Hi @rohanmate,

You have to click on the visual and go to the Roller bruch icon in the Visualisation pane.  Remov the Totals from there.  Now drag the measure in.

[rohanmate]

Hi v-qiuyu-msft,

Thank you so much for reverting back.

I tried applying the same logic in my power BI report but unfortunatly its not taking the data type associated with EARLIER command. Please find below the my logic applied;

Where:

Project Updates (2) - Table Name

Logged ByID- Id Column

Workstream Name - Project Name Column

OS Label - Status Column

PS = var t=CALCULATE(MAX('Project Updates (2)'[Logged ById]),FILTER('Project Updates (2)','Project Updates (2)'[Logged ById]<EARLIER('Project Updates (2)'[Logged ById])&& 'Project Updates (2)'[Workstream Name]=EARLIER('Project Updates (2)'[Workstream Name])))
return
LOOKUPVALUE('Project Updates (2)'[OS Label],'Project Updates (2)'[Logged ById],t)

Error Message while creating Custom Column

[v-qiuyu-msft]

Hi @rohanmate,

Based on your sample data, my DAX is working. Not sure if your real data is the similar as sample data. Would you please share the pbix file with us? If it does contain sensitive data, please upload report to your OneDrive and send the link to me in private message. 

Best Regards,
Qiuyun Yu

[rohanmate]

Hi @v-qiuyu-msft,

Can you also please help me with summarizing the total for status.

Thank You,

Rohan