Forum Discussion
Unproductive customer
Hi All,
In my Power BI report, we have only demention columns (e.g Customer name , code and location) and las invoice date(taken max invoice date from sales fact table). in this report we want to show only unproductive customer, means they has not made any sales in the selected date range. Example: (if we select date range in the slicer, 1s Sept to 30th sept) Customer 001 status is delivered then they are productive customer and customer status is cancelled, returned the they're unproductive customer. want to show only unproductive customer. Tried creating DAX but it's not working. Can anyone please help?
To report on things that are not there you need to use disconnected tables and/or crossjoins.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
6 Replies
To report on things that are not there you need to use disconnected tables and/or crossjoins.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Unproductive Customers =
VAR MaxInvoiceDate = MAX('Sales'[Invoice Date])
VAR SelectedStart = MIN('Date'[Date])
VAR SelectedEnd = MAX('Date'[Date])
RETURN
IF(
NOT(ISBLANK(MaxInvoiceDate)) &&
MaxInvoiceDate >= SelectedStart &&
MaxInvoiceDate <= SelectedEnd &&
[Status] IN {"Cancelled", "Returned"},
1,
0
)Then add a visual-level filter showing only where this measure = 1.
If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pandeHi Vidya_0505
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.Hi Vidya_0505
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank youHi All,
Thanks for your response.
The Unproductive customer report, we have to show the customer who has not done any sales in the selected month and full returned invoice. Here customer code is having both partial return and full return. If the document status is full return then only he is unproductive customer. if the customer is having partial and full return then he is productive custome. I tried creating measure. It's not working as expected, it's giving both results (Partial and full) I need to show only full returned customer details. Please check below measure and help me to do modifications.
UnproductiveCustomerFlag =
Var Invoicecount =
canculate(
distinctcount(fact secondary sales[ sal cashmemo no]),
fact secondary sales{key_flag] <>"PAYM"
)
Var FullReturnCount =calculate(
Distinctcount(fact secondary sales[ sal cashmemo no]),
fact secondary sales{key_flag] <>"PAYM",fact secondary sales[document status code] ="CM-0404",
Dim_cashmemo_type[Cashmemo type desc] ="full Return"
)
varPartalReturncount =fact secondary sales[ sal cashmemo no]),
fact secondary sales{key_flag] <>"PAYM",
fact secondary sales[document status code] ="CM-0404",Dim_cashmemo_type[Cashmemo type desc] ="Partial Return"
)
Return
IF( invoicecount .0 &&
FullReturncount >0&&
PartialReturnCount =0,
1,0)
Hi Vidya_0505 ,
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Regards
