Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more
Dear Power BI friends.
I'm wondering if you can help me with the following problem. Unfortunately, I cannot share any screenshots because of confidentiality, but I can share the code I used for the measure, and I have created an example.
My table contains these columns "Order ID," "Customer ID," and "Delivery Date." I want to determine whether or not a customer has placed an order after the delivery date of the order ID displayed.
I want to display a "1" if it's TRUE and "0" if FALSE, and then show the total so I can see the total amount of orders that have an order placed at a later date. In the table below there are 2 out of 3 orders have a subsequent order placed.
I'm able to get the correct 1's and 0's in the rows of the table using the measure pasted below. However, the total is incorrect, as shown in green in the example table below.
Order Booked After Delivery =
VAR _Current_Date = SELECTEDVALUE(Orders[Delivery Date])
VAR _Table =
ADDCOLUMNS(
DISTINCT(Orders[Customer ID]),
"Latest Order Date",
CALCULATE(
MAX(Orders[Order Date]),
ALLEXCEPT(Orders, Orders[Customer ID]),
),
"Current Date",
_Current_Date
)
VAR _Add_Control_Column =
ADDCOLUMNS(
_Table,
"Control",
IF([Latest Order Date] >= [Current Date] && [Latest Order Date] <> 0 && [Current Date] <> 0, 1, 0)
)
VAR _Sum = SUMX(_Add_Control_Column, IF([Control] > 0, [Control], 0))
RETURN
_Sum
I'm able to get the correct total by creating another measure referring to my first measure. However, this is not ideal. Here is the code for the extra measure:
Order Booked After Delivery SUMX =
SUMX(VALUES(Orders[Customer ID]), [Order Booked After Delivery])
Is there any way to modify my first measure to display the correct total? Ideally, I would like to have only one measure.
Thank you in advance.
Try this measure:
Order Booked After Delivery =
SUMX (
Orders,
VAR vDeliveryDate = Orders[Delivery Date]
VAR vRowCount =
CALCULATE (
COUNT ( Orders[Order ID] ),
ALLEXCEPT ( Orders, Orders[Customer ID] ),
Orders[Delivery Date] > vDeliveryDate
)
RETURN
IF ( NOT ISBLANK ( vRowCount ), 1 )
)
Proud to be a Super User!
The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!
| User | Count |
|---|---|
| 38 | |
| 36 | |
| 33 | |
| 30 | |
| 28 |
| User | Count |
|---|---|
| 128 | |
| 88 | |
| 79 | |
| 67 | |
| 62 |