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.
I have a table of data which tracks various cars and their costs over thier lifespan, which looks something like this:
Car - - - - - - - - - - - Date - - - - - - - - Category -- - Amount
Ford Mondeo --- 01/01/2016 --- Purchase - - - £5000
Ford Mondeo - - 08/08/2018 - - Repairs - - - - £300
Ford Mondeo - - 09/09/2018 - - Sale - - - - - - £3000
Toyata Yaris - - - 10/09/2018 - - Purchase - - - £4000
Toyata Yaris - - - 10/12/2018 - - Repairs - - - - £400
I am trying to work out the monthly cost of each car, and have created the following measure for months owned:
Months Owned = DATEDIFF(FIRSTDATE(Cars[Date]),LASTDATE(Cars[Date]),MONTH)
IF( car has been sold?? ,MONTH(TODAY()),LASTDATE(Cars[Date]))Thanks.
Solved! Go to Solution.
I already have a dates table but I didn't pesonally see the benefit of linking that to the measure I'm trying to create.
Since my post I have managed to create the measure and it seems to be working:
Months Owned = DATEDIFF(FIRSTDATE(Cars[Date]),IF(FILTER(VALUES(Cars[Category]),Cars[Category] = "Sale")="Sale",LASTDATE(Cars[Date]),TODAY()),MONTH)
First step is to get a dedicated calendar table and use the columns from that table in your measures,
https://docs.microsoft.com/en-us/dax/date-and-time-functions-dax
I already have a dates table but I didn't pesonally see the benefit of linking that to the measure I'm trying to create.
Since my post I have managed to create the measure and it seems to be working:
Months Owned = DATEDIFF(FIRSTDATE(Cars[Date]),IF(FILTER(VALUES(Cars[Category]),Cars[Category] = "Sale")="Sale",LASTDATE(Cars[Date]),TODAY()),MONTH)
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |