cancel
Showing results for
Did you mean:
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
Super User

@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!

5 REPLIES 5
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!

Helper V

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!

Super User

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

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!

Helper V

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

Super User

@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!

Announcements