cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerZs
New Member

Quaterly target change - lookup value based on the date defines the quarter

Dear PBI Community, I would like to have your support and ideas for the following topic. We got a quaterly changing target to our employees and that is against we would like to measure their performance. I would like to have the right target (defined for the quesrter) based on the date we are measuring against as an additonal column. Thanks

PowerZs_0-1653485817327.png

 

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @PowerZs ,

Here are the steps you can follow:

1. Create calculated column.

Delivery stop target =
SWITCH(
   TRUE(),
    "Q"&""&QUARTER('Table'[report_date])="Q3"&&'Table'[Loop_code]="B26",55,
    "Q"&""&QUARTER('Table'[report_date])="Q4"&&'Table'[Loop_code]="B26",60)

2. Result:

vyangliumsft_0-1653964512828.png

 

Best Regards,

Liu Yang

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

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

Hi  @PowerZs ,

Here are the steps you can follow:

1. Create calculated column.

Delivery stop target =
SWITCH(
   TRUE(),
    "Q"&""&QUARTER('Table'[report_date])="Q3"&&'Table'[Loop_code]="B26",55,
    "Q"&""&QUARTER('Table'[report_date])="Q4"&&'Table'[Loop_code]="B26",60)

2. Result:

vyangliumsft_0-1653964512828.png

 

Best Regards,

Liu Yang

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

johnt75
Super User
Super User

If you have a Quarter column on your date table, and your date table is in a one-to-many relationship with the report table, then you could add a new column like

Target =
var currentLoopCode = 'Table'[Loop code]
return LOOKUPVALUE('Target'[Target number], 'Target'[Quarter], RELATED('Date'[Quarter]), 'Target'[Loop code], currentLoopCode)

Thanks for your support. Will check and come back if that resolves the issue.

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors
Top Kudoed Authors