cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Dax for 30 days before and 30 days after

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!

1 ACCEPTED SOLUTION
Super User

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
)
)``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Super User

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
)
)``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.