Tracking effect of a change in the fact table

[george_p]

Hi all,

In my sales fact table, I have a column that indicates an event at a certain date. What I want to do is to take this date and compare my sales three days before and after this event. How can I achieve this?

[Anonymous]

@george_p,

I make a test using the following sample  table(Table5), create the measures below.

testdate = CALCULATE(MAX(Table5[Date]),FILTER(Table5,Table5[Event]="test"))

beforetest = CALCULATE(SUM(Table5[Sales Amount]),DATESBETWEEN(Table5[Date],[testdate]-3,[testdate]-1))

aftertest = CALCULATE(SUM(Table5[Sales Amount]),DATESBETWEEN(Table5[Date],[testdate]+1,[testdate]+3))

If the above steps don't help, please share dummy data of your table and post expected result.

Regards,
Lydia

[george_p · Accepted solution]

@Anonymous,

Thanks for the suggestion. What you proposed is not suitable for my model since I have more than one events in my Event column and want to have the measures for all my events. Meanwhile, I solved this myself by changing my model. I created a separate table for my events and in that table, I added columns for the "beforetest" and "aftertest".

Thanks for the hint though.