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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Katerina_SL
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:

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.

Thank you for all advices:)

 

4 REPLIES 4
Katerina_SL
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

 

v-jianpeng-msft
Community Support
Community Support

Hi, @Katerina_SL 

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

vjianpengmsft_0-1710487349446.png

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:

vjianpengmsft_1-1710487773218.png

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

Katerina_SL_0-1710752847947.png

 

Output:

Katerina_SL_1-1710752951380.png

 



Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.