topic Re: Average over dates for benchmark in DAX Commands and Tips

Average over dates for benchmark

EWBWEBB — Tue, 04 Oct 2022 10:50:23 GMT

Hi there

I am trying to understand the difference in sales pre and post event to see if there is any change.

I have payment files for 6 dates, in a single table

'SaleFiles'

With columns:

SalesValue,SaleName,MembershipDate,DateKey,Customer ID

Each customer ID may have multiple sales/refunds on the same day under different types of SaleName.

i.e:

£10,Membership,02/07/2022,44744,12345

£100,Ticket,02/07/2022,44744,12345

-£20,Refund,02/07/2022,44744,12345

I then have a date table:

The dates I have are: 7 May 2022, 4 June 2022, 02 July 2022, 30 July 2022, 27 Aug 2022, 24 Sep 2022

With Columns:

Date,DateKey

These are joined on the date key.

One (DateTable) to Many (SaleFiles)

What I want to be able to achieve is, what was the average value of Ticket sales over the 7th May & 4th June combined.
Then be able to put this into a table to show the difference between this average and each of the following dates.

I would hope to use, DateTable as my date in table or x-axis as this will filter other information elsewhere.

I would then also want to be able to drill through by customer to see if the value per customer has increased or decreased on each on after the 4th June.

I've tried the following but I can't seem to get it all to stick so I can see the difference.

It feels like this should be really simple but I've tweaked all the variations of FILTER | AND | ALL within the measure and for some reason it always ends up with a problem somewhere, either averaging every date seperately (not combining the first two) or returning blanks for any dates outside of the filter date.

**UPDATE**
I've got this far and the calculation works per customer until I try to display this over a date table.

Any help greatly appreciated.

Re: Average over dates for benchmark

lbendlin — Wed, 05 Oct 2022 00:01:42 GMT

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Average over dates for benchmark

EWBWEBB — Thu, 06 Oct 2022 10:19:57 GMT

Hi lbendlin

I can't seem to attach a file. Hope the below will suffice.

Date,DateKey

07/05/2022	44688
04/06/2022	44716
02/07/2022	44744
30/07/2022	44772
27/08/2022	44800
24/09/2022	44828

SalesValue,SaleName,MembershipDate,DateKey,CustomerID

668.6190094	Membership	07/05/2022	44688	DM10135
334.2732227	Membership	04/06/2022	44716	DM10135
686.3950474	Membership	02/07/2022	44744	DM10135
338.7135908	Membership	30/07/2022	44772	DM10135
676.3266122	Membership	27/08/2022	44800	DM10135
30.53928729	Ticket	24/09/2022	44828	DM10135
162.5897358	Membership	24/09/2022	44828	DM10135
41.17208482	Ticket	04/06/2022	44716	DM34851
-791.208116	Refund	02/07/2022	44744	DM34851
61.18740263	Ticket	02/07/2022	44744	DM34851
-384.412413	Refund	30/07/2022	44772	DM34851
479.2099203	Membership	30/07/2022	44772	DM34851
-720.964709	Refund	27/08/2022	44800	DM34851
86.16605538	Ticket	27/08/2022	44800	DM34851
100.9107386	Refund	24/09/2022	44828	DM34851
66.284255	Ticket	24/09/2022	44828	DM34851
-374.723553	Refund	02/07/2022	44744	DM34853
88.10315328	Ticket	02/07/2022	44744	DM34853
290.156434	Membership	30/07/2022	44772	DM34853
-194.973433	Refund	27/08/2022	44800	DM34853
15.38424038	Ticket	27/08/2022	44800	DM34853
175.180816	Membership	27/08/2022	44800	DM34853
198.816163	Membership	07/05/2022	44688	DM34861
-434.096583	Refund	02/07/2022	44744	DM34861
-223.012008	Refund	27/08/2022	44800	DM34861
907.0228001	Ticket	07/05/2022	44688	DM34871
333.9358515	Ticket	04/06/2022	44716	DM34871
564.2276465	Ticket	02/07/2022	44744	DM34871
908.2565283	Ticket	30/07/2022	44772	DM34871
335.7169272	Ticket	27/08/2022	44800	DM34871
566.0098346	Membership	27/08/2022	44800	DM34871
799.0188638	Ticket	24/09/2022	44828	DM34871

Expected Result

I would want to be able to filter all this by another table DimCustomer. Which contains the following

CustID	Location	Type	AgeGroup	SpendLimit
DM10135	UK	Gold	25-30	£25,000