DAX - calculate invoices

[Diego_Vialle]

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

[tamerj1 · Accepted solution]

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]

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?

[amitchandak]

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

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())