DATESBETWEEN- how to select the respective contract starts and end date from list of customer

[Anonymous]

Hi All,

Good day!

I am trying to solve two questions below:-

1. I am trying to write a DAX which calculates the total sales between contract start date and 31 Oct 2021 for respective customer. Each of the customers has own different contract start date in data table. May I know what DAX I should use in the "start date" below since it requires an expression?
   
   Contract start to current month = CALCULATE([Total Sales],DATESBETWEEN('Calendar'[Date], [Start Date], date(2021,10,31)))
   
   
2. If I want to import the 'end date' above from excel file whereby i would change the end date whenever i want in excel (consists of just one column and one value in one cell) and subsequently refresh the PBI, rather than going into PBI to edit the formula, what DAX should i use to replace the hardcoding "2021,10,31' above?

Thank you!

[v-robertq-msft · Accepted 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　ａ　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]

hi @v-robertq-msft 

Thank you so much for your suggestion. it is very helpful! 😀
Appreciate it.

[amitchandak]

@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

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

[Anonymous]

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!!