# Average when between two dates

Hi,

I want to be able to calculate the average order per customer in the first year since their signup date.

So

Calculate([Average Sales]) where the OrderDate greater than SignUpDate and < SignUpDate + 1 year.

Any assistance appreciated. Seems so simple but so confused.

I have come up with a non-elegant solution, as per MZdata2 file where I add a calculated column for signupdatre + 1 year, add another calculated column testing if order date > signup + 1 year, then use that column as a filter for average sales, but if anyone has a more elegant way of creating the average sales comparing dates Iâ€™d appreciate your method.

MZdata2.pbix (151.2 KB)

Thanks
John
MZdata.pbix (149.0 KB)
MZdata.xlsx (13.6 KB)

1 Like

Lot of different ways to handle this one, but I chose to do a bit of upfront work in Power Query to simplify the DAX.

Created two new columns in the fact table:

1. Signup + 1Yr = Date.AddYears( [Signup Date], 1 )
2. Within 1 Yr = if [Order Date] <= [#â€śSignup + 1Yrâ€ť] then â€śTRUEâ€ť else â€śFALSEâ€ť

Then made a couple of changes to your data model:

1. Marked the date table as a date table
2. Removed the Orders table from the model â€“ that table should not be filtering the Customer dimension

Then the DAX resulting is really simple:

``````Avg Sales Within 1 Yr =
CALCULATE(
AVERAGE( 'Fact'[Amount] ),
'Fact'[Within 1 Yr] = TRUE
)
``````

And hereâ€™s what it looks like all put together:

I hope this is helpful. Full solution file attached below.

â€“ Brian
eDNA Forum - MZdata solution.pbix (149.5 KB)

3 Likes

Thanks Brian. That is what I had in a new edition I posted after the initial post. So very glad you did it the same way. I was playing around with the merged queries so hadnâ€™t tidied up the example or marked a date table yet. I think the marked date table may have been an issue I was experiencing with other methods. A step I just forgot to do in my mock data table.

I was trying to find an averageX solution but was failing. It may have been due to the date table not being marked.

Thanks for you assistance. Most appreciated.

John

2 Likes

I think I need to sometimes ignore my â€śwork it out totally in dax without adding any calculated columnsâ€ť rule.

The extra model size is rarely an issue in my data world.

1 Like

Yup, lots of different ways to do this. If it werenâ€™t for the wonkiness of the leap year/February 29th problem, a simple measure wouldâ€™ve done the trick. I played around with DATEADD to accomplish the same thing as the M code, but in the end just felt doing it in Power Query was simplest and most transparent. If the fact table were gigantic, a measure might be the better way to go, but otherwise Iâ€™m happy with the solution path we both chose.

â€“ Brian

2 Likes

There are often good reasons to have added physical columns in your report (needed for visuals, slicing based on that field, executing a join based on that field, etc.). However, given that they are the same level of dynamic (update only on load or refresh), I almost always create custom columns in Power Query, rather than calculated columns in DAX - the reason being that custom columns will show up when you go back into PQ, while calculated columns will not.

For example, in my solution above, I could not have created the â€śWithin 1 Yrâ€ť field as is, because if â€śSignup + 1Yrâ€ť had been created as a calculated column, it would not be available for me to reference in the IF statement, even thought it would appear in the data model.

Come to think of it, pretty much the only time I create calculated columns is when Iâ€™m on the forum and I donâ€™t have the underlying data file available to go into PQ and create new columnsâ€¦

• Brian
2 Likes

Thanks for the extra explanation. I have had the â€śDoh! where did my calculated column goâ€ť experience when I went back into PQ and had to recreate in PQ before so that all makes sense. Thanks Brian.

1 Like