how to get prior users list with current users list in a table visual in power bi desktop

[Lahari]

Hi Everyone,

we are having list of employee and their roles with updated date and joining date. In Power BI desktop we need to have a table visual where we need to compare prior quarter employee with current quarter employee and want to highlight which employee has been inactive when compared this quarter with previous quarter. I need to know how to create that table by dax. please help me how to write a dax for this visual to create. we have done this table manually in excel by merging prior quarter with previous quarter that should be implemented in Power BI with table visual. This is the task we need to do in the Power BI desktop.

This is the raw data that is provided

In Power BI Desktop we need to show this in table visual and highlight when their is difference

Please help me how to get this in PBI Desktop.

Thanks in advance. 

[bhelou · Accepted solution]

Hello , here is the table i made as you want to be shown in power bi , kindly follow the steps in the power queries : 
1 : you should have a unique identifier ( if linked with another tables ) 
2 : in power Queries ( in the original table split the year from the quarter and make it as columns ) 
3: make the relationship correct one to many ( here i made 2 tables  : users & Roles ) , then i linked it to the original table 
4 : make a matrix and make it as stepped layout 
5 : drop the values for it in the matrix as per the picture 

Attached PBIX file 
https://drive.google.com/drive/folders/1rJ-SuGhhMJ81VxOnaOKwTIyzO_bYxWSs?usp=sharing 
kindly accept as a solution if it works with you . 

Regards ,

[v-stephen-msft · Accepted solution]

Hi @Lahari ,

After a period of research and experimentation, here is the final result.

Prior Period Roles = 
VAR _TODAY=TODAY()
VAR _DATE=EOMONTH(_TODAY,-3)
VAR _PREVIOUS=YEAR(_DATE)&" Q"&QUARTER(_DATE)
RETURN  CALCULATE(MAX('Table'[Roles]),FILTER('Table',[Row Load Date]=_PREVIOUS))

Current Period Roles = 
VAR _TODAY=TODAY()
VAR _CURRENT=YEAR(_TODAY)&" Q"&QUARTER(_TODAY)
RETURN  CALCULATE(MAX('Table'[Roles]),FILTER('Table',[Row Load Date]=_CURRENT))

Color measure is for conditional formatting.

Apply conditional table formatting in Power BI - Power BI | Microsoft Learn

Color = IF([Current Period Roles]<>[Prior Period Roles],"Yellow")

I know, you might ask if the fourth column could be removed. After my testing, this fourth column cannot be removed, if you really want to remove it, you can make the font of the fourth column consistent with the background color separately to achieve transparency.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Lahari]

Hi Thanks for the reply and I got the solution 

[bhelou · Accepted solution]

Hello , here is the table i made as you want to be shown in power bi , kindly follow the steps in the power queries : 
1 : you should have a unique identifier ( if linked with another tables ) 
2 : in power Queries ( in the original table split the year from the quarter and make it as columns ) 
3: make the relationship correct one to many ( here i made 2 tables  : users & Roles ) , then i linked it to the original table 
4 : make a matrix and make it as stepped layout 
5 : drop the values for it in the matrix as per the picture 

Attached PBIX file 
https://drive.google.com/drive/folders/1rJ-SuGhhMJ81VxOnaOKwTIyzO_bYxWSs?usp=sharing 
kindly accept as a solution if it works with you . 

Regards ,

[Lahari]

Hi 

Thanks for the reply. 

But I need the format which I have shown in second image. Can you please help me how to get that answer.