cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Diego_Vialle
Helper II
Helper II

DAX - Sum of the number of notes for each customer

I have a formula that calculates New Customers. Now I need to add the amount of "Outgoing Invoice" for each of them.

 

 

Novos Clientes = 
var vPrimeira_Compra = 
CALCULATETABLE(
    ADDCOLUMNS(
        VALUES(SBOPRODMS[Nome do PN]),
        "Data_Primeira_Compra", Calculate(MIN(SBOPRODMS[Data NF]))
    ),
    All(dCalendario),
    SBOPRODMS[Documento] = "Nota fiscal de saída"
)
var vResultado = 
FILTER(
    vPrimeira_Compra,
    [Data_Primeira_Compra] in VALUES(dCalendario[Date])
)
Return
Countrows(vResultado)

 

 

As an example below I have a customer with 11 invoices.

 

Diego_Vialle_0-1653744011666.png

 

 

 

1 ACCEPTED SOLUTION

@Diego_Vialle 

Sorry I thought each row repesents one invoice. Please try

Novos Clientes =
VAR vPrimeira_Compra =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( SBOPRODMS[Nome do PN] ),
            "Data_Primeira_Compra", CALCULATE ( MIN ( SBOPRODMS[Data NF] ) )
        ),
        ALL ( dCalendario ),
        SBOPRODMS[Documento] = "Nota fiscal de saída"
    )
VAR vResultado =
    FILTER (
        vPrimeira_Compra,
        [Data_Primeira_Compra] IN VALUES ( dCalendario[Date] )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( SBOPRODMS[N # Documento] ),
        FILTER (
            SBOPRODMS,
            SBOPRODMS[Nome do PN]
                IN SELECTCOLUMNS ( vResultado, "@Nome do PN", [Nome do PN] )
        )
    )

View solution in original post

7 REPLIES 7
tamerj1
Community Champion
Community Champion

Hi @Diego_Vialle 

please try

Novos Clientes =
VAR vPrimeira_Compra =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( SBOPRODMS[Nome do PN] ),
            "Data_Primeira_Compra", CALCULATE ( MIN ( SBOPRODMS[Data NF] ) )
        ),
        ALL ( dCalendario ),
        SBOPRODMS[Documento] = "Nota fiscal de saída"
    )
VAR vResultado =
    FILTER (
        vPrimeira_Compra,
        [Data_Primeira_Compra] IN VALUES ( dCalendario[Date] )
    )
RETURN
    COUNTROWS (
        FILTER (
            SBOPRODMS,
            SBOPRODMS[Nome do PN]
                IN SELECTCOLUMNS ( vResultado, "@Nome do PN", [Nome do PN] )
        )
    )

The amount of bills is higher, in the example below it would be 11 bills from this customer:

 

Diego_Vialle_0-1653749736456.png

 

Diego_Vialle_1-1653750823568.png

 

 

 

@Diego_Vialle 

Sorry I thought each row repesents one invoice. Please try

Novos Clientes =
VAR vPrimeira_Compra =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( SBOPRODMS[Nome do PN] ),
            "Data_Primeira_Compra", CALCULATE ( MIN ( SBOPRODMS[Data NF] ) )
        ),
        ALL ( dCalendario ),
        SBOPRODMS[Documento] = "Nota fiscal de saída"
    )
VAR vResultado =
    FILTER (
        vPrimeira_Compra,
        [Data_Primeira_Compra] IN VALUES ( dCalendario[Date] )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( SBOPRODMS[N # Documento] ),
        FILTER (
            SBOPRODMS,
            SBOPRODMS[Nome do PN]
                IN SELECTCOLUMNS ( vResultado, "@Nome do PN", [Nome do PN] )
        )
    )
Whitewater100
Solution Sage
Solution Sage

Hi:

The following pattern can work, although it's different in measuring new customers. For instance, you can determine the period of time in which you consider a customer new or lost. In my example below I will use 90 days as this figure. Then you will need just a basic measure that counts total invoices.

Total Invoice = SUM(Table[Invoice No.])

 

New Customer No. InvoicesSales =
VAR CustomerTM = VALUES( Sales[Customer ID] )
VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer ID] ),
FILTER( ALL( Dates[Date] ),
Dates[Date] > MIN( Dates[Date] ) - 90 &&
Dates[Date] <= MIN( Dates[Date] ) ) )

RETURN
CALCULATE( [Total Invoice],
EXCEPT( CustomerTM, PriorCustomers ) )
 
To figure new customers count with this method:
 
New Customers = //calculating which customers within any particular month have purchased but haven't done so for the last x number of days
VAR CustomerTM = VALUES( Sales[Customer ID] )
VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer ID] ),
FILTER( ALL( Dates ),
Dates[Date] > MIN( Dates[Date] ) - 90 &&
Dates[Date] < MIN( Dates[Date] ) ) )

RETURN
COUNTROWS(
EXCEPT( CustomerTM, PriorCustomers ) )
 
That -90 can be put in a parameter and the user can then select how many days for considering new of lost. That's off topic but available.
 
I hope this helps!

Thanks for the answer, but I already tried to implement it with this parameter of 90 days and it doesn't work when using a Date filter.

Hi Diego:

That sounds like your model may need to be star Schema?

The date filters work nicely, so something else might be going on? You can avoid omplicated DAX if you do the data modeling piece. I'll past a somple star schema below and a snipet of a report showing how it all comes together, especially using date filters.

Whitewater100_1-1653748874414.png

 

 

Whitewater100_0-1653748596712.png

 

Can I send the dataset to your email?

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors