The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024. 2-for-1 sale on June 20 only!
Register NowFind everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
92 | |
90 | |
72 | |
69 |
User | Count |
---|---|
232 | |
128 | |
117 | |
82 | |
82 |