Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
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"
)
)
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"
)
)
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?
@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())
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
28 | |
21 | |
11 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |