Dont Understand DAX Code Received in Forum Update

DAX DAX Calculations
1

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!

2

Is it possible to see a demo model of where this has been used.

Or was there a previous forum posts this refers to?

I think an easier answer could likely be found with the DATEDIFF function

Finding it difficult to understant all the factors at play just with the formula at the moment.

Thanks
Sam

3

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.

Thanks!

4

Ok sure thing.

Sometimes we post a lot of video links into the forum at once that’s why I’m not sure of every formula from every video.

I’m presuming it’s this one

image
image1234×477 52.7 KB

As always it’s the initial context of the calculation which is very important here, which is basically every order ID in the sales tables.

What MAX(Indexcolumn) is doing is just working out what row number we are in for every row is this table.

image
image827×479 26.7 KB

image
image779×381 25.3 KB

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.

Hopefully that explains things further for you.

Chrs
Sam