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

[PeterStuhr]

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?

[vanessafvg]

@PeterStuhr   are you able to provide some data?

[PeterStuhr]

Hi @vanessafvg 

It could look like this:

Ordernr	OnboardingDate	OrderDate	Segment	OrderIntake
1	01-01-2018	01-03-2016	A	1000
2	11-01-2019	03-08-2016	A	1240
3	06-08-2017	09-08-2016	A	3453
4	14-01-2019	09-08-2016	C	1119
5	05-01-2018	08-01-2017	B	4523
6	13-04-2019	08-01-2017	A	9999
7	15-01-2019	10-01-2017	B	29999
8	03-08-2016	06-08-2017	A	2343
9	12-01-2019	04-01-2018	A	1256
10	04-01-2018	07-01-2018	A	2134
11	10-01-2017	02-07-2018	C	2305
12	07-01-2018	11-01-2019	B	10000
13	08-01-2017	12-01-2019	B	2330
14	16-10-2019	15-01-2019	C	39999
15	02-07-2018	01-02-2019	A	2000
16	09-08-2016	13-04-2019	C	5430
17	17-01-2019	16-10-2019	A	55999

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

Thanks!

[vanessafvg]

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?

and then dra

[PeterStuhr]

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:

Company	Onboarding Date	Segment
Test1	01-01-2016	A
Test2	02-01-2016	A
Test3	03-01-2016	A
Test4	04-01-2017	B
Test5	05-01-2017	B
Test6	06-01-2017	B
Test7	07-01-2018	C
Test8	08-01-2016	B
Test9	09-01-2019	A
Test10	10-01-2019	C
Test11	11-01-2019	B
Test12	12-01-2019	A

Order Intake table:

OrderDate	Company	EUR
11-01-2019	Test1	1000
12-01-2019	Test2	2000
03-01-2016	Test1	5000
04-01-2017	Test1	50
11-01-2019	Test2	100
12-01-2019	Test10	300
07-01-2018	Test3	2000
08-01-2016	Test4	2000
09-01-2019	Test2	3000
10-01-2019	Test2	1000
11-01-2019	Test3	2000
12-01-2019	Test1	4000

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

[vanessafvg · 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" )
    )
)