Forum Discussion

Diego_Vialle's avatar
Diego_Vialle
Icon for Helper II rankHelper II
4 years ago
Solved

DAX - calculate invoices

Hello, I have a formula that filters customers who NEVER bought, but BUY in the last two months. Now I need the quantity of these invoices, each one has a different document number. I tried to change it within the formula to "Nº Documento" (which is the invoice number), without success.
Below is the formula used and the value in red that I need to find:

 

 

Qtd. Faturamento Bimestral =
VAR AllOldCustomers =
CALCULATETABLE (
VALUES ( SBOPRODMS[Nº Documento] ),
SBOPRODMS,
SBOPRODMS[Data NF]
< TODAY () - 60,
SBOPRODMS[Documento] = "Nota fiscal de saída"
)
VAR AllNewCustomers =
CALCULATETABLE (
VALUES ( SBOPRODMS[Nº Documento] ),
SBOPRODMS[Data NF]
< TODAY () ,
SBOPRODMS[Documento] = "Nota fiscal de saída"
)
RETURN
COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) )
DAX
DAX. CALCULATE
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Diego_Vialle 

    you may tey

     

    Qtd. Faturamento Bimestral =
VAR AllOldCustomers =
    CALCULATETABLE (
        VALUES ( SBOPRODMS[Customer] ),
        SBOPRODMS[invoice date]
            < TODAY () - 60,
        SBOPRODMS[document] = "outgoing invoice"
    )
VAR AllNewCustomers =
    CALCULATETABLE (
        VALUES ( SBOPRODMS[Customer] ),
        AND (
            SBOPRODMS[invoice date]
                >= TODAY () - 60,
            SBOPRODMS[document] = "outgoing invoice"
        )
    )
VAR NewCustomers =
    EXCEPT ( AllNewCustomers, AllOldCustomers )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( SBOPRODMS[Nº Documento] ),
        FILTER (
            SBOPRODMS,
            SBOPRODMS[Customer]
                IN NewCustomers
                && SBOPRODMS[document] = "outgoing invoice"
        )
    )

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Diego_Vialle , Just change the duration and follow approach in my blog

     

    Customer Retention Part 5: LTD Vs Period Retention
    https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497

     

     

    Rolling 2 =
    var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _min = Date(Year(_max), month(_max) -2, Day(_max))+1,
    return
    CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

     

    LTD =
    var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())

    var _max= Date(Year(_max), month(_max) -2, Day(_max)),
    var _min = Minx(ALLSELECTED('Date'),'Date'[Date])
    return
    CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

     

    How bought in last two month only

    countx(values(Customer[Customer]) , if(isblank([LTD]) && not(isblank([Rolling 2])) , [Customer], Blank())

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Diego_Vialle 

    you may tey

     

    Qtd. Faturamento Bimestral =
VAR AllOldCustomers =
    CALCULATETABLE (
        VALUES ( SBOPRODMS[Customer] ),
        SBOPRODMS[invoice date]
            < TODAY () - 60,
        SBOPRODMS[document] = "outgoing invoice"
    )
VAR AllNewCustomers =
    CALCULATETABLE (
        VALUES ( SBOPRODMS[Customer] ),
        AND (
            SBOPRODMS[invoice date]
                >= TODAY () - 60,
            SBOPRODMS[document] = "outgoing invoice"
        )
    )
VAR NewCustomers =
    EXCEPT ( AllNewCustomers, AllOldCustomers )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( SBOPRODMS[Nº Documento] ),
        FILTER (
            SBOPRODMS,
            SBOPRODMS[Customer]
                IN NewCustomers
                && SBOPRODMS[document] = "outgoing invoice"
        )
    )

     

     

    • Diego_Vialle's avatar
      Diego_Vialle
      Icon for Helper II rankHelper II
      4 years ago

      Good afternoon friend, thanks again for your help. I believe it is correct.

      In the Invoicing chart, when inserting the year and month of the Calendar table on the axis, the measure is being broken. How do I relate the Measurements table with the Calendar table?