Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I hope someone can help be with the following issue:
I have a table with Order ID, Ordre date and Delivery date. I would like to count how many orders in a given period are also delivered in same periode. Eg. if I choose jan-feb 2018, I want to know how many orders have both order date and delivery date in this period.
I have a date table which is connected to the order date - and I know how to used date table and date slicers 🙂
Tove
Solved! Go to Solution.
Hi Tove,
One way of doing this is to use TREATAS to apply the Date filter to the Delivery date column as well.
Assuming your tables are called Orders and Date, the measure would look like this:
Orders Delivered in Same Period = CALCULATE ( DISTINCTCOUNT ( Orders[Order ID] ), KEEPFILTERS ( TREATAS ( VALUES ( 'Date'[Date] ), Orders[Delivery date] ) ) )
I assumed you wanted DISTINCTCOUNT of Order ID, but change as needed.
KEEPFILTERS is also there as a safeguard in case you are filtering on Delivery date, to ensure we stay within the current Delivery date filter context.
Out of interest - good article on TREATAS here.
Regards,
Owen
Hi Tove,
One way of doing this is to use TREATAS to apply the Date filter to the Delivery date column as well.
Assuming your tables are called Orders and Date, the measure would look like this:
Orders Delivered in Same Period = CALCULATE ( DISTINCTCOUNT ( Orders[Order ID] ), KEEPFILTERS ( TREATAS ( VALUES ( 'Date'[Date] ), Orders[Delivery date] ) ) )
I assumed you wanted DISTINCTCOUNT of Order ID, but change as needed.
KEEPFILTERS is also there as a safeguard in case you are filtering on Delivery date, to ensure we stay within the current Delivery date filter context.
Out of interest - good article on TREATAS here.
Regards,
Owen
Thank you very much - it works and I have learn two new DAX formulas 🙂
Tove
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |