DAX problem : Tracking sales from a certain point in time ? variable date per customer

Vander1981

New Member
Joined
Jun 20, 2017
Messages
18
Hello Guys,

I would like to ask your help on a DAX problem that I am facing :

I would like to measure the impact of an event on Customers Sales. That event could take place at different time depending of the Customer.

the measure would track sales for a period of 6 months starting from the date of the event (variable for each customers) and compare it to another 6 month period (just before the event).


The problem is that I have no clue at the moment, on how to take into account the date of the event that is stored in my Customer table.


I have a star schema model : Fact table with relationships to different dim tables (Calendar, Customer, Product, geography). I could adapt the model if necessary.
NB : There is no Relationship between Customer table and calendar table.


Thanks a lot for your help :)
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
What is the final output? Table with a list of customers and the values? If so, start with SELECTEDVALUE ( Customers[EventDate] ) to get the date of the event.

You then want to use some of the date functions, DATESBETWEEN () and DATESADD for example I create the range required. Final measures would look like:

CALCULATE (
[Sales],
{Date expression that you created}
)

Have two versions, one for the 6 months before and this be for the 6 months after, and 6 months before.
 
Upvote 0
Thank you Gazpage. A table with customers and the values is indeed the final output.

The solution you give makes sense to me but how would you relate the event date (customer table) with the calendar table as no active relationship between them can be set in my model.

Calendar table has a relationship with fact table,
Cutomer table has a relationship with fact table (customer key from both table). That prevent me to set an active relationship between calendar table and customer table (which contain the event date column).

Should I try a function like USERELATIONSHIP to activate it during the time of the calculation ? Create a better model if feasible ... ? ... ?

In advance thank you for any insight.
 
Upvote 0
I wouldn’t bother, unless you have real performance issues with my approach.

As a starter use this formula.

CALCULATE (
[SumOfSales],
FILTER (
ALL ( Calendar[Dates] ),
Calendar[Dates] = SELECTEDVALUE ( Customer[Dates] )
)
)

This will return the sales literally for the event date. Then just need to amend this approach but using DATESBETWEEN or equivalent.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top