I recently received an example from Sam I do not understand. Not afraid to say it. This is about calculating the number of days between dates. The code:(Copied hastily)

=
var indexnum = max(data(index))
var previous index = calculate(max data(index), filter allselected(data) data(index) < indexnum
var currentdate = value(selectedvalue(data(purchasedate))
var prior date = value(calculate(selectedvalue(data(purchase date), filter(all(data) data(index) = previous index
return
if (indexnum = calculate(min(data(index), allselected(data) ),0, current date - prior date

The if statement makes no sense to me. In the variable the indexnum is initially set to MAX(index). When the If is executed, it is assumed indexnum is looking at the first selected index(MIN)……not the MAX thus returning 0. I am new to DAX…what am I missing about how this statement works? How is Indexnum set each time if it always starts at MAX? Thanks!

This code came from you from a video called advanced Dax Calculations. You were finding the number of days between purchases. I received it as part of a forum update email from Enterprise DNA a day or two ago. You already have all of the data.

I just don’t get how the variables could work given how they start with the max index. Could really use an explanation.

This is how we then filter the table (all virtually) so that we only then look at the subset of data we want to for every single result.

There’s no doubt there’s a bit to this formula…what I would do to really understand it is break it down, so that all the variable are actually measures. That way you can see each result side by side. This is what I always do, and then occasionally I place them into a more effecient formula like this - but not always, sometimes I just leave them as seperate measure which is all good

Overall here we are trying to dynamically work out at every row in the table what is the current date of purchase and what was the previous one, then looking to find the different between those two.

We need to adjust the context of the calculation at every row to do this, that’s why I’ve used CALCULATE here.