I have a set of data that is simply dates of events happening with the duration of each event and the end date of the previous event to calculate the number of days between them:
I’m trying to create something that will predict the next x number of events, perhaps 10, based on this data. This should be based on finding the average duration of each event and the average days between each event, both of which I have as measures. My plan is to add the avg days between to the latest end date to get the start date of the 4th event, then add the avg duration to get the end date and so on. I imagine it should be a loop but I can’t get my head around doing a loop in DAX.
So for example using my data above, the avg duration is 6 and avg days between is 20. Taking the latest record of 19/06/21 - 25/06/21 from there my next 10 results would be:
I’ve found a way around this I believe, probably not the most elegant solution but it works!
Using my MAX(Last End Date) measure as an anchor of sorts, the predicted dates can be found using differing amounts of the avg duration and avg between days measures. e.g. the first predicted start date is (0 * Avg Duration) + (1 * Avg Between) from the MAX(Last End Date). The first predicted end date is (1 * Avg Duration) + (1 * Avg Between) from the MAX(Last End Date).
So I’ve created a table with an ID column going 1 - 10. Then I have 2 calculated columns as:
Start Date = [Last Event End Date] + ( ( Predictions[ID] - 1 ) * [Avg Duration] ) + ( Predictions[ID] * [Avg Days Between] )
For the end date it’s simple, the ID multiplied by each measure:
End Date = [Last Event End Date] + ( Predictions[ID] * [Avg Duration] ) + ( Predictions[ID] * [Avg Days Between] )
That then gives me the results I expected. Below is the PBIX in case it’s any use to anyone!
Nice solution! Thanks for sharing it with the forum.
If you’re interested, another potential approach might be to do this as a recursive M function. In her epic writeup for Challenge #5, @Melissa provides the code for a recursive function in a somewhat similar context.
