Dax for 30 days before and 30 days after

[JimSales81]

Hello,

My company has a new pricing program.  Each customer can enter the program by signing a new contract (i.e. each customer's entry date will be different).  I want to normalize everything by creating a measure to show 30 days before and 30 days after contract signing date.  

I.e. if Customer A signed contract on 4/15, I want the forumla to identify each sale within 30 days before or after, calculate how many days from the contract signing date it is and plot that point on a line chart. 

What i'm hoping to get is:

if Customer A sold 10k on 4/17 that is +2 days from contact signing .  If they sold 3K on 4/10 that is -5

If customer B, whose contract date was 5/10 sold 30K on 5/12 it is also +2 days from contract signing.   If they sold 2k on 5/5 that is -5.  

When i plot the chart the orgin would be 0 and +2 would be 40K and -5 would be 5k.

Thanks for any help!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
One of ways to achieve this is to create a calculated column in the fact table.

Please check the below pictures and the attached pbix file.

date_offset_by_entry_date_CC =
VAR _customer = sales_fact[customer]
VAR _entrydate =
    MAXX (
        FILTER ( customer_dimension, customer_dimension[customer] = _customer ),
        customer_dimension[entry_date]
    )
RETURN
    INT ( sales_fact[date] - _entrydate )

And then, create a measure with having the calculated column on the x-axis.

Sales expected result: = 
CALCULATE (
    SUM ( sales_fact[sales] ),
    FILTER (
        sales_fact,
        sales_fact[date_offset_by_entry_date_CC] >= -30
            && sales_fact[date_offset_by_entry_date_CC] <= 30
    )
)

[vicky_]

Good Ol SLQBI has a recent article that covers something very similar: https://www.sqlbi.com/articles/comparing-cumulative-values-for-events-in-different-periods/

In particlar, I would look at the Preparing the Model section and adapt that to suit your needs.