Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
EWBWEBB
Helper II
Helper II

Average over dates for benchmark

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.

 

EWBWEBB_0-1664802317584.png

 

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.

EWBWEBB_0-1664880555934.png

 

 

Any help greatly appreciated.

2 REPLIES 2
lbendlin
Super User
Super User

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

Hi @lbendlin 

 

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

Date,DateKey

07/05/202244688
04/06/202244716
02/07/202244744
30/07/202244772
27/08/202244800
24/09/202244828

 

SalesValue,SaleName,MembershipDate,DateKey,CustomerID

668.6190094Membership07/05/202244688DM10135
334.2732227Membership04/06/202244716DM10135
686.3950474Membership02/07/202244744DM10135
338.7135908Membership30/07/202244772DM10135
676.3266122Membership27/08/202244800DM10135
30.53928729Ticket24/09/202244828DM10135
162.5897358Membership24/09/202244828DM10135
41.17208482Ticket04/06/202244716DM34851
-791.208116Refund02/07/202244744DM34851
61.18740263Ticket02/07/202244744DM34851
-384.412413Refund30/07/202244772DM34851
479.2099203Membership30/07/202244772DM34851
-720.964709Refund27/08/202244800DM34851
86.16605538Ticket27/08/202244800DM34851
100.9107386Refund24/09/202244828DM34851
66.284255Ticket24/09/202244828DM34851
-374.723553Refund02/07/202244744DM34853
88.10315328Ticket02/07/202244744DM34853
290.156434Membership30/07/202244772DM34853
-194.973433Refund27/08/202244800DM34853
15.38424038Ticket27/08/202244800DM34853
175.180816Membership27/08/202244800DM34853
198.816163Membership07/05/202244688DM34861
-434.096583Refund02/07/202244744DM34861
-223.012008Refund27/08/202244800DM34861
907.0228001Ticket07/05/202244688DM34871
333.9358515Ticket04/06/202244716DM34871
564.2276465Ticket02/07/202244744DM34871
908.2565283Ticket30/07/202244772DM34871
335.7169272Ticket27/08/202244800DM34871
566.0098346Membership27/08/202244800DM34871
799.0188638Ticket24/09/202244828DM34871

 

Expected Result

EWBWEBB_0-1665051298658.png

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

CustIDLocationTypeAgeGroupSpendLimit
DM10135UKGold25-30£25,000

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors