Cumulative value with Userelationship

[PwrBI01]

Hi guys,

I have the following table (it contains the measure 'Pendiente de cobro'):

 

And I would like to obtain the following one:

The main problem is that the table calendar 'T_Calendar' and the data table 'T_FacturacionExpedienteResumida' are not directly relationated, so I used the function Userelationship, as you can see:

The measure is the following one:

Pendiente de cobro = 
Var Facturado = CALCULATE(SUM(T_FACTURACIONEXPEDIENTERESUMIDA[TOTALAPAGAR]);T_FACTURACIONEXPEDIENTERESUMIDA[DIASSINCOBRAR]>0;USERELATIONSHIP(T_CALENDARIO[Fecha];T_FACTURACIONEXPEDIENTERESUMIDA[FECHAFACTURA]))
Var Cobrado = CALCULATE(SUM(T_FACTURACIONEXPEDIENTERESUMIDA[ENTRADA]);T_FACTURACIONEXPEDIENTERESUMIDA[DIASSINCOBRAR]>0;USERELATIONSHIP(T_CALENDARIO[Fecha];T_FACTURACIONEXPEDIENTERESUMIDA[FECHAFACTURA]))
Var PendienteCobro = Facturado-Cobrado
Return
PendienteCobro

How can I change it to get the cumulative values?

Thanks in advance.

[PwrBI01]

Hi @VijayP 

Thank you for your answer but it does not work as I expected.

I made some changes:

1. Put the relationship as inactive because I need to have it like this.

2. Create a new measure called 'Test 4' which gives the following results:

The result for 2019 is working correctly, but it is not the case for 2017 and 2018, because the results should be the following ones:

I think the problem is that the measure is not applying correctly the maximum date for each year (the part I put in bold):

Test 4 =

Var Facturacion = CALCULATE(SUM('T_FACTURACIONEXPEDIENTERESUMIDA'[TOTALAPAGAR]);'T_FACTURACIONEXPEDIENTERESUMIDA'[DIASSINCOBRAR]>0)

Var Cobro = CALCULATE(SUM('T_FACTURACIONEXPEDIENTERESUMIDA'[ENTRADA]);'T_FACTURACIONEXPEDIENTERESUMIDA'[DIASSINCOBRAR]>0)

Var PendienteCobro = Facturacion-Cobro

Var PendienteCobroAcumulado = CALCULATE(PendienteCobro;USERELATIONSHIP(T_FACTURACIONEXPEDIENTERESUMIDA[FECHAFACTURA];T_CALENDARIO[Fecha]);FILTER(ALLSELECTED(T_CALENDARIO[Fecha]);T_CALENDARIO[Fecha]<=MAX(T_CALENDARIO[Fecha])))

Return

PendienteCobroAcumulado

The link to the file is https://www.dropbox.com/s/kip4r60nztzkzmc/Prueba%20Power%20BI.pbix?dl=0

Thanks in advance

Regards

[PwrBI01 · Accepted solution]

Hi guys,

I have solved the problem.

The measure I need to introduce is:

Pendiente de cobro =

Var Facturado = CALCULATE(SUM('T_FACTURACIONEXPEDIENTERESUMIDA'[TOTALAPAGAR]);'T_FACTURACIONEXPEDIENTERESUMIDA'[DIASSINCOBRAR]>0;USERELATIONSHIP(T_CALENDARIO[Fecha];'T_FACTURACIONEXPEDIENTERESUMIDA'[FECHAFACTURA]);FILTER(ALL(T_CALENDARIO[Fecha]);T_CALENDARIO[Fecha]<=MAX(T_CALENDARIO[Fecha])))

Var Cobrado = CALCULATE(SUM('T_FACTURACIONEXPEDIENTERESUMIDA'[ENTRADA]);'T_FACTURACIONEXPEDIENTERESUMIDA'[DIASSINCOBRAR]>0;USERELATIONSHIP(T_CALENDARIO[Fecha];'T_FACTURACIONEXPEDIENTERESUMIDA'[FECHAFACTURA]);FILTER(ALL(T_CALENDARIO[Fecha]);T_CALENDARIO[Fecha]<=MAX(T_CALENDARIO[Fecha])))

Var PendienteCobro = Facturado-Cobrado

Return

PendienteCobro

[VijayP]

@PwrBI01 

Look at the Formula for TEST 3 to get your solution

PBIX Attached

[VijayP]

@PwrBI01 

Sample Data would help to give more clear solution. Appreciate Your Kudos!

[PwrBI01]

Hi @VijayP, 

I send you a sample data (this is not the original one, which is much bigger), so I need to keep the not activated relationship between both tables.

The link is https://www.dropbox.com/s/kip4r60nztzkzmc/Prueba%20Power%20BI.pbix?dl=0

Thanks in advance.

[VijayP]

@PwrBI01 

This is the formula to create Virtual Relationship

Revenue based on delivery date = CALCULATE([Total Revenue],USERELATIONSHIP(Dates[Date],Sales[Delivery Date]))

 Cumulative Delivery =

CALCULATE([revenue based on delivery date],FILTER(ALL(Dates),Dates[Date]<=MAX(Dates[Date])))

 Hope this helps!

[PwrBI01]

Hi @VijayP,

Thank you for your answer, I tried it but it doesn't work.

Regards.