Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have an issue which I cannot make it dynamic. I have to calculate sum of Quantity and Order value for open orders based on the date selected in the slicer. Any help is much appreciated! 🙂
Below is the sample data :-
OrderNo | Status | Productkey | OrderDate | OrderChangeDate | Quantity | Value |
100 | Open | 2 | 20201005 | 20201005 | 10 | 5000 |
100 | Invoiced | 2 | 20201005 | 20201114 | 0 | 0 |
101 | Open | 5 | 20201009 | 20201009 | 20 | 1200 |
101 | Open | 5 | 20201009 | 20201114 | 20 | 1200 |
101 | Invoiced | 5 | 20201009 | 20201204 | 0 | 0 |
Result of measure | ||||
Date(is dynamic from the slicer selection) | Measure_Qty | Measure_Value | ||
20201009 | 30 | 6200 | (Both 100 and 101 are open) | |
20201114 | 20 | 1200 | (Only 101 is open) | |
20201204 | 0 | 0 | (No orders are open) |
I don't see any 20201009 under order 100 in your source data. sth is contradictory here? Would be nice if you were specific on the date column in the slicer too.
If I read your intentions well, this should work:
Measure_value =
CALCULATE(
SUM('Table'[Value]),
'Table'[Status] = "Open"
)
Measure_qty =
CALCULATE(
SUM('Table'[Quantity]),
'Table'[Status] = "Open"
)
Best,
Pawel
Hi,
Maybe I should explain better.
If data selection on slicer is date 20201009 so that date both orders 100 and 101 are Open and not invoiced so the order value will be the sum of both orders.
Order number 100 was invoiced on 20201114. So on 20201114 only value for open order 101 will be shown.
The measure you mentioned would not work as I select the date from slicer and based on that all order which are open till that date should be summarized and showed in the measure value.
Best Regards,
PG
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |