Forum Discussion

PwrBI01's avatar
PwrBI01
Post Patron
5 years ago
Solved

Cumulative value with Userelationship

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 cal...
Need Help
  • PwrBI01's avatar
    PwrBI01
    5 years ago

    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
PwrBI01's avatar
PwrBI01
Post Patron
5 years ago

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's avatar
PwrBI01
Post Patron
5 years ago

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