Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
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] )
)
)
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:
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] )
)
)
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.])
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.
Can I send the dataset to your email?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |