Forum Discussion
Helper IIFiltering two different dates by used og one date slicer
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
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
2 Replies
- OwenAuger
Super User
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
Fia123Thank you very much - it works and I have learn two new DAX formulas :-)
Tove