Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Lahari
Helper II
Helper II

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

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

Lahari_0-1678972410810.png

 

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

 

Lahari_1-1678972449527.png

 

Please help me how to get this in PBI Desktop.

 

Thanks in advance. 

2 ACCEPTED SOLUTIONS
bhelou
Responsive Resident
Responsive Resident

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 ,

bhelou_0-1678975426648.png

 

View solution in original post

v-stephen-msft
Community Support
Community Support

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

vstephenmsft_0-1679562588232.png

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.           

View solution in original post

4 REPLIES 4
v-stephen-msft
Community Support
Community Support

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

vstephenmsft_0-1679562588232.png

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.           

Hi Thanks for the reply and I got the solution 

bhelou
Responsive Resident
Responsive Resident

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 ,

bhelou_0-1678975426648.png

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors