Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a simple query, on a count, but not sure which of these functions I should; Count; COUNTAX; I would like to accomplish below; I would like to show this month by month.
Metadata:
Bought Date ==>The date the car was bought for sale (stock)
Sold Date ==>The date car was sold
Return Date ==> The date car was returned
I would like to ::::Count Cars in stock =Sold Date -Return date
Expected answer: Sold 3 cars and 1 returned car = 2 (Count Cars in stock)
Please below Dummy date
Car ID Bought Date Sold Date Return date (unhappy customer id)
1 01/01/2020 11/01/2020
2 02/01/2020 12/01/2020
3 06/01/2020 10/01/2020 12/02/2020
4 10/01/2020
5 15/01/2020
Thanks in advance
Solved! Go to Solution.
Hi @MYDATASTORY
You can create a date dimension ( if you haven't got one ) create relationships between all dates and later use USERELATIONSHIP function in your measures to count the events.
Date dimension example
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns
USERELATIONSHIP
@MYDATASTORY , connect all three dates with a common date table, there will one active and two inactive joins. Use userelation to handle that.
Refer example how two handle more than one dates with the same date table.
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
Hi @MYDATASTORY
You can create a date dimension ( if you haven't got one ) create relationships between all dates and later use USERELATIONSHIP function in your measures to count the events.
Date dimension example
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns
USERELATIONSHIP
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |