Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Thank you!
Solved! Go to Solution.
Hi,
According to your description, I can roughly understand your requirement, I think you can use the Filter() function to simply achieve your needs, you can try these measures:
Contract Start =
CALCULATE(MIN('Contract details'[Contract Date]),FILTER(ALL('Contract details'),[Customer]=MAX('Contract details'[Customer])))
Sales from Contract Start =
CALCULATE(SUM('Contract details'[Sales amount]),FILTER(ALL('Contract details'),[Contract Date]>=[Contract Start]&&[Contract Date]<=DATE(2021,12,31)&&[Customer]=MAX('Contract details'[Customer])))
And you can place them into a table chart to get what you want, like this:
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
According to your description, I can roughly understand your requirement, I think you can use the Filter() function to simply achieve your needs, you can try these measures:
Contract Start =
CALCULATE(MIN('Contract details'[Contract Date]),FILTER(ALL('Contract details'),[Customer]=MAX('Contract details'[Customer])))
Sales from Contract Start =
CALCULATE(SUM('Contract details'[Sales amount]),FILTER(ALL('Contract details'),[Contract Date]>=[Contract Start]&&[Contract Date]<=DATE(2021,12,31)&&[Customer]=MAX('Contract details'[Customer])))
And you can place them into a table chart to get what you want, like this:
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Try a measure like
sales =
var _max1 = maxx(allselected('Date'),'Date'[Date])
var _min1 = minx(allselected('Date'),'Date'[Date])
var _max2 = minx(allselected('Sales'),'Sales'[Contract Start])
var _min2 = maxx(allselected('Sales'),'Sales'[Contract End])
var _min = max(_min1, _max2)
var _max = min(_max1,_min2)
return
calculate(sumx(values(Sales[customer]),[Total Sales]), filter('Date','Date'[Date] >=Min && 'Date'[Date]<=_max))
or check if these can help
Hi @amitchandak
Thank you for your advise. Unfortunately I am very new to Power BI and I couldn't really understand the logic of the measures you suggested.
Basically I have two sets of data. First is the sales table in the past 2 years and second is the contract details for each customer as shown below. I would like to calculate the sales from the start date of contract up to 31 Oct 2021 for each customer. Can I use the Datesbetween function? If so, what expresison should I use to replace the "Start Date" in the formula below?
Contract start to current month = CALCULATE([Total Sales],DATESBETWEEN('Calendar'[Date], [Start Date], date(2021,10,31)))
Can i actually replace it with Max('Contract details' [Contract Start]), since it only has one start date for each customer?
thank you!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |