Returning last date and date before the last

[frognz]

Hello,
For info, I'm still pretty new with PowerPivot.

I'm trying to work out the last date a customer made a purchase as well as the penultimate date (the date before the last).
To take a simple example, let's say Tbl Sales has 3 columns:
Customer | Trandate | Qty
A | 01/01/2016 | 15
A | 02/01/2016 | 52
A | 03/01/2016 | 25
A | 04/01/2016 | 12
A | 05/01/2016 |20
B | 01/01/2016 |50
B | 03/01/2016 | 20
B | 05/01/2016 | 15

First measure:
LastSaleDate:=MAX(Sales[TranDate])

Second measure:
Penultimate1:==CALCULATE(MAX(Sales[TranDate]),FILTER(Sales,Sales[TranDate]<MAX(Sales[TranDate])))

This works fine. But I have recently learned that I can use a measure as an argument for FILTER, so I try this, based on samples found around the web and in books:
Penultimate2:=CALCULATE(MAX(sales[TranDate]),FILTER(All(Sales),Sales[TranDate]<LastSaleDate))

This returns (Blank). And I do not understand! I have been trying many days now to take the LastSaleDate and use it in the filter of the second measure but whatever I have tried has either returned blank or error.

Could anyone tell me what I do wrong here?

Thank You
Christine

 

[Matt Allington]

This is really complex and is one of the most important concepts for people to learn (along with filter propagation). I cover both topics in my book Learn to Write DAX - Excelerator BI

Filter is an iterator and it has a row context (a row context simply means that it "knows" which row it is in and it can iterate them one at a time). But a row context doesn't automatically create a filter context (a filter context will filter the tables in the model). These two topics are too big to cover in depth here, but I will try to give you the general idea. In the second measure, you are iterating the sales table - 1 row at a time inside a row context. There is a row context but no filter context, so at each step of the process the sales table is not filtered by the filter iterator. If you want to filter the sales table at each step of the process, you add an additional calculate inside the formula as shown below.
=calculate(max(sales[transdate]),filter(sales,sales[transdate]>calculate(max(sales[transdate])))

in in this new formula, filter goes to the first row in the sales table, then the additional calculate forces context transition - the row context is converted to a filter context - and so before the test is completed, the sales table is then filtered so that only that 1 row is visible. After it is filtered, then the test is carried out. sales[transdate]>max(sales[transdate]). Given there is only 1 row in the newly filtered sales table, the date can never be greater than the max of the same date. As the filter function works through all the rows in the sales table (1 at a time) the sales table gets filtered by the additional calculate, there is only ever 1 row in the sales table when the test is carried out, and hence there are never any rows that pass the test - result = blank

so why am I telling you this? Because a measure has an invisible calculate wrapped around it that you cannot see. So replacing a formula with a measure inside an iterator is not exactly the same even though it is reasonable that you think it is.

when you replace max(sales[transdate]) with [LastSaleDate], what you are really doing is replacing it with calculate(max(sales[transdate]) and that is why it doesn't work. In this case it will never work, so you need to use the raw formula.

i use measures inside formulas all the time. Just be aware that if you use one inside an iterator, you will often get different behaviour.

 

[frognz]

Hi Matt,

And thank you from "across the ditch" (I'm not a native but a french import there, also a certified MS office trainer for 20 years).

What you are saying starts to make sense and I appreciate your explanations. Yes, I am still struggling with the row/filter contexts, and although I understand what they mean, it is their impact on some DAX functions that causes me some troubles. I tested many things and worked out that it was not the first part of the calculate that caused the error but the filter part of it. I also found an article describing why EARLIER causes similar issues, due to iterations and row context. I'm fairly comfortable with SQL and especially Access, so when I encounter an issue like that one, I try to solve it with a db system, the logic being fairly similar I think. And I couldn't do it either. So it ended up that something fairly straightforward on the paper wasnt actually that straightforward

Again, thank you for the enlightment I'm going back to my book and get a deeper understanding or row/filter context

Christine

Optional question not really related to the post itself: do you have many people in OZ interested in MS BI or just BI in general ? I see the benefit of it for businesses but it seems that very few here know anything about BI, so I feel quite alone talking about the topic.

 

[Matt Allington]

I didn't realise your user name was 2 words - frog and NZ. Now it makes sense

i think BI is very popular, but maybe some companies don't call it BI. Maybe they call it Reporting or something else. SSAS Multidimensional is still predominant, but tabular is growing. If you were starting from scratch today, you would probably go tabular, but of course not many companies are starting from scratch. Also even if they could replace their multidimensional system for 10% of the original MD install price, they don't want to write off the asset.

Very few people know about Power Pivot and Power BI. Lots need to know, but for some reason it has never made their radar. If I could work for everyone that has the potential to benefit from these tools, I would be very very busy. But given most don't know about it, it is hard work trying to enlighten everyone and convince them how good it is - they just think you are a snake oil salesman like everyone else. So I don't spend muck time trying to convert the unconverted. Instead I just focus on the enlightened few and try to work for them. I predict Power BI will change all this in the next 6-12 months.
Power BI Public Story Telling is Pure Genius - Excelerator BI

 

[frognz]

The name is a bit of a joke here. You know, being French ... in the land of the All Blacks .... I think that says it all. So I promote my origins proudly

I was pleased to see your name on the reply to my post. Want it or not, you are a bit of a profile in the world of MS BI.

Later this year I will start teaching all this because the need is definitively there. I do agree that many larger businesses will already have a working analysis and reporting solution but there is all that world of small to medium businesses who are still living at the neoderthal age of computing. Every day I have people in my classroom saying "it would be nice if we could do this or that" and my answer is always the same "actually, you can". After having heard that one time too much last year, I embarked myself on this ship. And I promote it by doing free seminars. The topic is not BI centric but about modern ways of working. I present SharePoint, mobile devices, data analysis that can be done by the end-user (living in a smaller city, there aren't that many companies who can afford big data solutions so most people who will have to do some data analysis are non specialists, "simple" Excel users - this not being deragotary). And you know what? I actually starts to bear its fruits! We have increasing demands on all those products.

Which makes me think that you may have a solution to another issue I have but here is not the place for it so I will send a message through your website.

Thanks again for the help on my initial question
Christine