Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all!
I would like to create either a column or measure which returns the difference of distance run on a recorded event date.
Here's a sample source table:
Company Name | Employee | Recorded Date | Difference (distance run on recorded date) | Total distance run (km) |
A | Bob | 2019-12-01 | 2 | 2 |
A | Bob | 2019-12-02 | 5 | 7 |
A | Jack | 2019-12-03 | 3 | 10 |
B | Hannah | 2019-12-02 | 4 | 4 |
B | John | 2019-12-05 | 3 | 7 |
Ideally, I would like to take data from the difference column to make a visual that represents the average distance ran on recorded date (average of the difference between two rows values) for each company or employee.
What would be the best way to approach this? Would I need Power Query for this?
Thank you!
Solved! Go to Solution.
OK, you can create the column you requested using EARLIER to filter ALL of your table and then using MAXX on that to grab the date (this gets you your previous row). Then you could use that date to filter out to the value for the last run. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395.... Let me know if you need further clarification but the technique is pretty much there. Although you might have an extra step to grab the previous run's distance.
Some like this as mentioned by @Greg_Deckler
difference of distance run on a recorded event date =
var _last = maxx(filter(table,table[Company Name]=earlier(table[Company Name]) && table[Recorded Date]<earlier(table[Recorded Date]) ),table[Total distance run (km)])
return
if(is(_last),table[Total distance run (km)], _last-table[Total distance run (km)])
@nerokasai , There are couple of solutions provided. Hope something has worked out for you
OK, you can create the column you requested using EARLIER to filter ALL of your table and then using MAXX on that to grab the date (this gets you your previous row). Then you could use that date to filter out to the value for the last run. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395.... Let me know if you need further clarification but the technique is pretty much there. Although you might have an extra step to grab the previous run's distance.
I am not 100% following this. Does the data displayed represent sample source data? Or is it what you would like to achieve? If it is sample source data, what is your expected output?
@Greg_Deckler My apologies, I should have better clarified!
The data shown displays the overall expected output I want to have.
To be more precise, I would like the column "Difference (distance run on recorded date") added to the sample source data, such that the overall data table would be like what I showed.
Some like this as mentioned by @Greg_Deckler
difference of distance run on a recorded event date =
var _last = maxx(filter(table,table[Company Name]=earlier(table[Company Name]) && table[Recorded Date]<earlier(table[Recorded Date]) ),table[Total distance run (km)])
return
if(is(_last),table[Total distance run (km)], _last-table[Total distance run (km)])
User | Count |
---|---|
102 | |
84 | |
77 | |
70 | |
67 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |