Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Fia123
Helper II
Helper II

Filtering 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

1 ACCEPTED SOLUTION
OwenAuger
Super User
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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you very much - it works and I have learn two new DAX formulas 🙂

 

Tove

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.