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 with repair data for vehicles. I need to calculate what was the smallest and largest timeframe (in days) between them. Let's say I have a date slicer on my report, and after selecting dates I have the following raw data:
I would then like to show a Power BI table that looks something like this:
Based on the data, min date diff would be 11 days and max date diff would be 42 days.
So the question is, what is the best way of doing this? My first instinct was to create a measure with EARLIER thrown in, but I don't really understand how EARLIER works so couldn't figure out how to do it. The other option that I thought of was to create a new calculated column with the difference to previous repair, something like this:
This way I could just create simple measures like: Min date diff = MIN(Repairs[Days from previous]). Problem is that I don't really know how to write such column efficiently without slowing the data refresh, and I would really prefer using a measure anyways if that's possible.
Solved! Go to Solution.
@JKoivu , diff from previous date
As column
From previous repair =
datediff (MaxX(filter(Table,[Vehicle_id] =earlier([Vehicle_id]) && [Date] <earlier([Date])),[Date]), [Date], day)
As measure
From previous repair measure =
datediff (MaxX(filter(allselcted(Table),[Vehicle_id] =max([Vehicle_id]) && [Date] <max([Date])),[Date]), [Date], day)
@JKoivu , You can get measures like this
Measure 1 =
datediff(max([Date]), calculate(max([Date]), allexcept(Table,[Vehicle_id])), day)
Measure 2 =
datediff( calculate(min([Date]), allexcept(Table,[Vehicle_id])),max([Date]), day)
or you can get columns
Column 1 =
datediff(([Date]), calculate(max([Date]), filter(Table,[Vehicle_id] =earlier([Vehicle_id]))), day)
Column 2 =
datediff( calculate(min([Date]), filter(Table,[Vehicle_id] =earlier([Vehicle_id]))),([Date]), day)
@amitchandak , thanks for you reply! I tried these with the following results: Measure 1 (which I figured is the max diff?) returns 0, and the measure 2 returns 346, so these don't work like I imagined.
This is the result for the columns:
The Column 1 is pretty close to what I need, which is cool! But it seems to calculate datediff from the latest, is it possible to have it subtract the value from previous row?
@JKoivu , diff from previous date
As column
From previous repair =
datediff (MaxX(filter(Table,[Vehicle_id] =earlier([Vehicle_id]) && [Date] <earlier([Date])),[Date]), [Date], day)
As measure
From previous repair measure =
datediff (MaxX(filter(allselcted(Table),[Vehicle_id] =max([Vehicle_id]) && [Date] <max([Date])),[Date]), [Date], day)
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |