cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PeterStuhr
Helper V
Helper V

Calculate an average Order Intake for "this year" based on 2 different dates

Hi !

 

I need to calculate an "Average Order Intake" for "this year" - rolling to work next year as well.

 

I have this data:

Segmentation (A,B,C)

OrderStartDate (dates)

OnboardingDate (dates)

Order Intake Value (EUR)

 

It needs these 3 filters:

1. OrderStartDate (should be this year)

2. OnboardingDate (Should be this year)

3. Segment (SegmentA)

 

I simply can't make the measure work. Anyone who can help?

1 ACCEPTED SOLUTION

@PeterStuhr   sorry this sounds confusing, based on number of accounts, or number of companies?

 

also i get a blank result from your data because there are no company accounts onboarded with segment a in 2019

 

this is the measure i used

Avg Order Intake By Company =
CALCULATE (
    AVERAGE ( 'Order Intake'[EUR] ),
    FILTER (
        'Order Intake',
        YEAR ( 'Order Intake'[OrderDate] ) = YEAR ( TODAY () )
    ),
    FILTER (
        onboarding,
        YEAR ( onboarding[Onboarding Date] )
            YEAR ( TODAY () && onboarding[Segment] = "A" )
    )
)




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
vanessafvg
Super User
Super User

@PeterStuhr   are you able to provide some data?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hi @vanessafvg 

 

It could look like this:

 

OrdernrOnboardingDateOrderDateSegmentOrderIntake
101-01-201801-03-2016A1000
211-01-201903-08-2016A1240
306-08-201709-08-2016A3453
414-01-201909-08-2016C1119
505-01-201808-01-2017B4523
613-04-201908-01-2017A9999
715-01-201910-01-2017B29999
803-08-201606-08-2017A2343
912-01-201904-01-2018A1256
1004-01-201807-01-2018A2134
1110-01-201702-07-2018C2305
1207-01-201811-01-2019B10000
1308-01-201712-01-2019B2330
1416-10-201915-01-2019C39999
1502-07-201801-02-2019A2000
1609-08-201613-04-2019C5430
1717-01-201916-10-2019A55999

 

So I would like to calculate an average Order Intake for Segment A, for this year (both order date and onboarding date) 🙂

 

Thanks!

ok @PeterStuhr 

 

i might be simplifying this

 

but basically i just created a card,  set the aggregation to average and then dragged my filterds into the visual level filters and used relative date filtering for the dates -   what are you expecting?

 

Capture.PNGcapture1.PNG

 

and then dra





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hi again

 

Actually I explained this wrong.

 

It is 2 tables I have "related" to each other.

 

One of the tables contains the "Company" data. And the other one the Order Intake Data.

 

So instead of "average" of the order intake, the average should be based on the number of accounts.

 

Company table:

 

CompanyOnboarding DateSegment
Test101-01-2016A
Test202-01-2016A
Test303-01-2016A
Test404-01-2017B
Test505-01-2017B
Test606-01-2017B
Test707-01-2018C
Test808-01-2016B
Test909-01-2019A
Test1010-01-2019C
Test1111-01-2019B
Test1212-01-2019A

 

Order Intake table:

 

OrderDateCompanyEUR
11-01-2019Test11000
12-01-2019Test22000
03-01-2016Test15000
04-01-2017Test150
11-01-2019Test2100
12-01-2019Test10300
07-01-2018Test32000
08-01-2016Test42000
09-01-2019Test23000
10-01-2019Test21000
11-01-2019Test32000
12-01-2019Test14000

 

So what I want: Average Order Intake Per Company. Filter: 2019 in both date fields, and Segment A. 🙂

@PeterStuhr   sorry this sounds confusing, based on number of accounts, or number of companies?

 

also i get a blank result from your data because there are no company accounts onboarded with segment a in 2019

 

this is the measure i used

Avg Order Intake By Company =
CALCULATE (
    AVERAGE ( 'Order Intake'[EUR] ),
    FILTER (
        'Order Intake',
        YEAR ( 'Order Intake'[OrderDate] ) = YEAR ( TODAY () )
    ),
    FILTER (
        onboarding,
        YEAR ( onboarding[Onboarding Date] )
            YEAR ( TODAY () && onboarding[Segment] = "A" )
    )
)




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors