Average when between two dates

DAX DAX Calculations
1

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
2

@jgriffit ,

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:

image
image739Ă—310 26.4 KB

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

– Brian
eDNA Forum - MZdata solution.pbix (149.5 KB)

3 Likes
3

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
4

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
5

@jgriffit ,

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
6

@jgriffit ,

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
7

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