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

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

7 Replies

  • VijayP's avatar
    VijayP
    Community Champion
    5 years ago

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

      Hi VijayP,

       

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

       

      Regards.

  • VijayP's avatar
    VijayP
    Community Champion
    5 years ago

    PwrBI01 

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

  • 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