Question to identify discrepancies

[Babycakes_00]

Hi THere 

A little vague but hoping you can assist.

I have a data set with Customer ID, Customer Name which is the core list of customers.  On my other sheet is Customer Orders by date.

I am trying to create a table that you can filter by date.

If  I select Date 1/7/2024, I want to see that customer 1 did not make an order etc etc

So I can see all customers to ensure they have put in their regular order.

For example I created a query that merged with all matching and non matching which works if you do it on a daily basis , but you cant do this where you have orders over multiple days. 

How can I do it so I filter today and i can see all customers and I have a match to date for customer record, and I return blank if no order.

Customer Id	Customer Name	Address
1	Bob
2	Lisa
3	Ted
4	John

Customer ID	Date	Product	Quantity
1	1/7/2024
2	8/7/2024
1	8/7/2024
3	1/7/2024
4	1/7/2024

[v-hashadapu]

Hi @Babycakes_00 , Just wanted to know if your issue is solved. If not please share the details, so that we can work together to solve it. If it is solved, then please consider marking it 'Accept as Solution' so others with similar queries may find it easily.

[v-hashadapu]

Hi @Babycakes_00 , Thank you for reaching out to Microsoft Fabric Community Forum.

Can you please let me know if your issue is solved. If it is not, please share the details.

if it is solved, please consider marking it 'Accept as Solution'. 

[p45cal · Accepted solution]

You've posted this in the Power Query section so here's a PQ offering:

If a date doesn't appear in the Date slicer, no-one's placed any orders.

re: "I want to see that customer 1 did not make an order etc etc"

If you're not bothered about the customer name then a direct pivot from the Orders table is possible; see pivot at cell H27.

The workbook: https://app.box.com/s/qqgqq8hwpfz8gickojhdhftsay70cdo1

[v-hashadapu]

Hi @Babycakes_00 ,Thank you for reaching out to Microsoft Fabric Community Forum.

If you want to create a report that shows all customers with the date they placed an order, and you want to be able to filter by a specific date (e.g., 1/7/2024) to see if each customer has placed an order on that date, try this:

1. Let’s assume you have two tables:

(Table1) Customers & (Table2) Orders

Customers: Contains Customer ID, Customer Name, Address, etc.

Orders: Contains Customer ID, Order Date, Product, Quantity, etc.

2. Ensure there's a relationship between the Customer ID in the Customers table and the Customer ID in the Orders table. If not, create a relationship.
3. You might want to have a dedicated date table to help with filtering. If you don’t have one, create a Date table:

DateTable = CALENDAR(MIN(Orders[Date]), MAX(Orders[Date]))

4. Make sure you relate this Date table to the Orders table using the Order Date.
5. let's create a measure that will check whether a customer has placed an order on the selected date.

Order on Selected Date =

VAR SelectedDate = SELECTEDVALUE(DateTable[Date])

VAR CustomerOrder =

    CALCULATE(

        MAX(Orders[Date]),

        Orders[Date] = SelectedDate

    )

RETURN

    IF(ISBLANK(CustomerOrder), BLANK(), CustomerOrder)

6. Add a Table visual report to your visual.
7. Add Customer Id, Customer Name & the measure to the fields of table visual.
8. Add a Date slicer to filter by the date you want. When you select a specific date, the table will update to show All customers, The order date for those who placed an order on that date, The order date for those who placed an order on that date.
9. Your output will be something like this:

Customer ID	Customer Name	Order on Selected Date
1	Bob	1/7/2024
2	Lisa	(Blank)
3	Ted	1/7/2024
3	John	1/7/2024

If you think this post helps, please mark it as Accept as Solution, so others with similar queries may find it more easily.

[saud968]

You can achieve this in Power BI by creating a table that shows all customers and their order status for a selected date. Here’s how you can do it:

Step-by-Step Guide
Load Data:

Load both your customer list and order list into Power BI.
Create Relationships:

Ensure there is a relationship between the Customer ID in the customer list and the Customer ID in the order list.
Create a Date Slicer:

Add a slicer to your report and set it to filter by the order date.
Create a Calculated Table:

Use DAX to create a calculated table that combines the customer list with their order status for the selected date.
Example DAX Code
Customer List:

Customer ID | Customer Name | Address
1 | Bob |
2 | Lisa |
3 | Ted |
4 | John |
Order List:

Customer ID | Date | Product | Quantity
1 | 1/7/2024 | |
2 | 8/7/2024 | |
1 | 8/7/2024 | |
3 | 1/7/2024 | |
4 | 1/7/2024 | |
Create a Calculated Table:

Go to Modeling > New Table and enter the following DAX formula:
CustomerOrders =
ADDCOLUMNS(
'Customer List',
"Order Status",
IF(
COUNTROWS(
FILTER(
'Order List',
'Order List'[Customer ID] = 'Customer List'[Customer ID] &&
'Order List'[Date] = SELECTEDVALUE('Order List'[Date])
)
) > 0,
"Ordered",
"No Order"
)
)
Add the Table to Your Report:

Add the CustomerOrders table to your report and include the Customer ID, Customer Name, and Order Status columns.
Filter by Date:

Use the date slicer to filter the CustomerOrders table by the selected date.
This setup will allow you to filter by any date and see which customers have placed an order and which have not.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Babycakes_00]

Thank you - this is only giving me no order results.  I am not getting and ordered?