Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to 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
)
)
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.
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.
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.
User | Count |
---|---|
20 | |
19 | |
15 | |
10 | |
7 |
User | Count |
---|---|
28 | |
28 | |
13 | |
12 | |
12 |