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

[Vander1981]

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

 

[gazpage]

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.

 

[Vander1981]

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.

 

[gazpage]

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.

 

[Vander1981]

Great ! That’s perfect !
Indeed, no need to link both tables
Thanks a lot Gazpage.