UserRelationship and Variables: calculate Win and Open Sales

[Anonymous]

Hi people

I need to calculate the value of win sales and open sales.

In my model I have a main table of opportunities (sales) with all sales, both won and open (pipeline). It has several dates:

1. Created date: is the date the sale was created
2. Closing date: is the date when the sale is likely to close. That I consider the open sales pipeline.
3. Win date: is the date of the completed sale, won.

I made the model considering that the main relationship is the date won. (I don't know how I did it right, but just thinking about changing it gives me chills)

So when I'm calculating the value of the sale won, the measure considers the active relationship for the Win date.

For this measure I am using variables, as shown below:

MRR Split =

VAR Tipo_de_Venda = SELECTEDVALUE(Oportunidade[Tipo de Venda])

VAR MRR = SELECTEDVALUE(Oportunidade[MRR__c])

VAR SplitPercentage = SELECTEDVALUE('Divisão de oportunidade'[SplitPercentage])

VAR Preco_negociado = SELECTEDVALUE('Itens da Linha de Cotação'[Preco_negociado_com_impostos__c])

VAR Qtd_Produto = SELECTEDVALUE('Itens da Linha de Cotação'[Quantity])

VAR Total_Preco_Negociado = SELECTEDVALUE('Itens da Linha de Cotação - Total Negociado'[Total_negociado])

RETURN

IF(Tipo_de_Venda = "Up Sell" ,

(DIVIDE(Preco_negociado,Total_Preco_Negociado)*MRR)*(SplitPercentage*0.01) ,

(Preco_negociado*Qtd_Produto)*(SplitPercentage*0.01))

I now need to do the same calculation considering the closing date. I've tried all the ways to use the USERRELATIONSHIP function and I'm not getting results.

Could someone help me please?

Best regards

[V-pazhen-msft · Accepted solution]

@Anonymous 

Not very clear about exact output, but for userelationship try something like"

Measure = IF('Oportunidade'[Tipo de Venda]= "xxxx",
Calculate(DIVIDE(Preco_negociado,Total_Preco_Negociado)*MRR*(SplitPercentage*0.01),UseRelationship(Column1, Column2),
Calcuate((Preco_negociado*Qtd_Produto)*(SplitPercentage*0.01),UseRelationship(Column1, Column2)

Basicially you put the expression after Calculate, and UseRelationship, The two columns must have an inactive relationship to be activiated by UseRelationship.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , you have to do it using a common date table. Please refer if example in this blog can help

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

[Anonymous]

@amitchandak 

Thanks for your response.

In fact, I already have a calendar table in the model.

It is related to the three dates I mentioned. See image 1.

I circled the three dates relationships. The first one that is active is the win date.

My difficulties are how to trigger the other dates with the userrelatioship in the formula that I mentioned where the variables are.

I tried to use calculate after the return, but it didn't work.

again, tahnks your help.