Skip to main content
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

Helper II
Helper II

catch 5th order with exact average profit

Hello Everyone!


I would like to ask you help to solve this task. 
I have the table in Power BI with columns:



Order Creation Time



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.

Thank you for all advices:)


Helper II
Helper II

I've solved myself. Thank you.

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
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 =
        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 =
        SELECTEDVALUE ( Sheet1[User_ID] ),
        FILTER (
            ALLSELECTED ( Sheet1 ),
            'Sheet1'[Client_ID] = EARLIER ( Sheet1[Client_ID] )
                && 'Sheet1'[Order Creation Time] = _previousTime
    IF (
        'Sheet1'[User_ID] = _perviousID,
        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.




How to Get Your Question Answered Quickly

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.

Thank you for your respond.

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

You will find screenshort of possible input data and output.

Thank you very much for all your advices.

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






Helpful resources

Europe Fabric Conference

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 Carousel June 2024

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