Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I have two tables CustInvoiceJour , CustInvoiceTrans are connected by the InvoiceId field in both tables.
I added a slicer for InvoiceDate from the CustInvoiceTrans table. I want to identify customers who have no sales for three previous months from the date selected in the slicer.
Solved! Go to Solution.
Thanks for the reply from Greg_Deckler, please allow me to provide another insight.
Hi @amal_01 ,
Please refers to the following steps.
The test data are as follows.
Use the following DAX to create a measure to identify customers in the previous three months.
ISInPre3Months =
VAR _selectedDate = CALCULATE(MAX('CustInvoiceTrans'[InvoiceDate]),
ALLSELECTED(CustInvoiceTrans[InvoiceDate]),
ALLEXCEPT(CustInvoiceTrans,CustInvoiceTrans[InvoiceDate]),
REMOVEFILTERS('CustInvoiceJour'))
VAR _tb = CALCULATETABLE('CustInvoiceTrans', DATESINPERIOD('CustInvoiceTrans'[InvoiceDate],_selectedDate,-3,MONTH))
RETURN
MAXX(FILTER(_tb,[InvoiceId]='CustInvoiceTrans'[InvoiceId]),[InvoiceId])
Use the following DAX to create a measure that calculates sales per customer for the previous three months.
Sales for previous 3 months =
VAR _selectedDate = CALCULATE(MAX('CustInvoiceTrans'[InvoiceDate]),
ALLSELECTED(CustInvoiceTrans[InvoiceDate]),
ALLEXCEPT(CustInvoiceTrans,CustInvoiceTrans[InvoiceDate]),
REMOVEFILTERS('CustInvoiceJour'))
VAR _tb = CALCULATETABLE('CustInvoiceTrans', DATESINPERIOD('CustInvoiceTrans'[InvoiceDate],_selectedDate,-3,MONTH))
RETURN
IF([ISInPre3Months]<>BLANK(),
SUMX(FILTER(_tb,[InvoiceId]='CustInvoiceTrans'[InvoiceId]),[LineAmount])
)
Add this two measures to the visual's filter pane and set them to “Is not blank” and "Is blank".The final result is as follows.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply from Greg_Deckler, please allow me to provide another insight.
Hi @amal_01 ,
Please refers to the following steps.
The test data are as follows.
Use the following DAX to create a measure to identify customers in the previous three months.
ISInPre3Months =
VAR _selectedDate = CALCULATE(MAX('CustInvoiceTrans'[InvoiceDate]),
ALLSELECTED(CustInvoiceTrans[InvoiceDate]),
ALLEXCEPT(CustInvoiceTrans,CustInvoiceTrans[InvoiceDate]),
REMOVEFILTERS('CustInvoiceJour'))
VAR _tb = CALCULATETABLE('CustInvoiceTrans', DATESINPERIOD('CustInvoiceTrans'[InvoiceDate],_selectedDate,-3,MONTH))
RETURN
MAXX(FILTER(_tb,[InvoiceId]='CustInvoiceTrans'[InvoiceId]),[InvoiceId])
Use the following DAX to create a measure that calculates sales per customer for the previous three months.
Sales for previous 3 months =
VAR _selectedDate = CALCULATE(MAX('CustInvoiceTrans'[InvoiceDate]),
ALLSELECTED(CustInvoiceTrans[InvoiceDate]),
ALLEXCEPT(CustInvoiceTrans,CustInvoiceTrans[InvoiceDate]),
REMOVEFILTERS('CustInvoiceJour'))
VAR _tb = CALCULATETABLE('CustInvoiceTrans', DATESINPERIOD('CustInvoiceTrans'[InvoiceDate],_selectedDate,-3,MONTH))
RETURN
IF([ISInPre3Months]<>BLANK(),
SUMX(FILTER(_tb,[InvoiceId]='CustInvoiceTrans'[InvoiceId]),[LineAmount])
)
Add this two measures to the visual's filter pane and set them to “Is not blank” and "Is blank".The final result is as follows.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-denglli-msft
thanks alot .
What if I want to find customers who de not have any sales from period date , meaning if I selected from 1/1/2024 to 30/3/2024, want to see customers who do not have any sales in this range date, meaning they do not have any purchase invoice
How do I write DAX that does this for me ?
can you help me Please ?
@amal_01 Create a table VAR for customers in the current month. VAR __Table1 = SELECTCOLUMNS( FILTER( ALL('Table'), [Date] >= EOMONTH( MAX('Table'[Date]), -1 ) + 1 && [Date] <= MAX('Table'[Date]) ), "Customer", [Customer] ). Create a table VAR for customers in the previous three months. VAR __Table2 = SELECTCOLUMNS( FILTER( ALL( 'Table' ), [Date] < EOMONTH( MAX('Table'[Date]), -1 ) + 1 && [Date] >= EOMONTH( MAX('Table[Date]), -4 ) + 1 ), "Customer", [Customer] ). Then do an EXCEPT. EXCEPT( __Table1, __Table2 )
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
11 |