cancel
Showing results 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

Helper II

catch 5th order with exact average profit

Hello Everyone!

I have the table in Power BI with columns:

Client_ID

Order

Order Creation Time

User_ID

I need to catch the moment and give reward to user who did at least 5 orders for 1 client and average profit for this 5 orders is 50 euros. I need to give this reward only once for 1 client.

For example, If 1 user was working with 1 client and did 5 orders in April 2023 with average profit for this 5 orders 50 euros he will get reward in April 2023. In May 2023 he will not get reward already for this client. But if he did 5 order with average profit 40 euros (what is not enough) and in May 2023 he will increase this average profit till 50 (even if orders will be more than 5), he can get this reward in May 2023.

All functions which I could write I can use because there are too many orders and don't have enough capacity to solve how I expected.

4 REPLIES 4
Helper II

I've solved myself. Thank you.

Community Support

Hi,  @Katerina_SL

I'm so sorry for my late reply. Can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Jianpeng Li

Community Support

Hi, @Katerina_SL

Based on your description, I have created the following sample data:

In this sample, 1004 received 10 orders in January 2023 and 10 orders in May 2023, then 1004 should be rewarded in January and no longer rewarded in May. Using the following DAX expression, I created a calculated column:

``````Is reward =
VAR _previousTime =
CALCULATE (
MAX ( 'Sheet1'[Order Creation Time] ),
FILTER (
ALLSELECTED ( Sheet1 ),
'Sheet1'[Client_ID] = EARLIER ( Sheet1[Client_ID] )
&& 'Sheet1'[Order Creation Time] < EARLIER ( Sheet1[Order Creation Time] )
)
)
VAR _perviousID =
CALCULATE (
SELECTEDVALUE ( Sheet1[User_ID] ),
FILTER (
ALLSELECTED ( Sheet1 ),
'Sheet1'[Client_ID] = EARLIER ( Sheet1[Client_ID] )
&& 'Sheet1'[Order Creation Time] = _previousTime
)
)
RETURN
IF (
'Sheet1'[User_ID] = _perviousID,
"No",
IF ( 'Sheet1'[Order] > 5, "Yes", "No" )
)
``````

Here are the results:

I've provided the PBIX file for this time below, and it would be great if it would help you.

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

Helper II

But I see that I didn't explain clearly my task.

You will find screenshort of possible input data and output.

The date when I will start to check this is April 2023 (if Client did any orders before, we are not calculating this client at all).
Input:

Output:

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

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

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors