DAX latest higher sales: When was the last time we sold more than today & how much was it?

[ImkeF]

Hi there,
no luck with web search for this one - hope someone can help me here:

I need 2 measures:

1) The last date when sales where higher than what my [SalesMAX] measure returns (so actually not today but the filter context of my SalesMAX measure )
2) The sales amount that corresponds to 1)

LinkToFile

Any help very much appreciated - thank you!

 

[Ozeroth]

Hi Imke,

Here is one way of doing it:

[SIZE=3][B]Last Date When Higher[/B] :=[/SIZE]
CALCULATE (
    LASTDATE ( DimDate[Date] ),
    FILTER (
        ALL ( DimDate ),
        DimDate[Date] < MIN ( DimDate[Date] )
            && [SalesMAX]
                > CALCULATE ( [SalesMAX], VALUES ( DimDate[Date] ) )
    )
)

[SIZE=3][B]Last Sales When Higher[/B] :=[/SIZE]
CALCULATE (
    CALCULATE ( [SalesMAX], LASTDATE ( DimDate[Date] ) ),
    FILTER (
        ALL ( DimDate ),
        DimDate[Date] < MIN ( DimDate[Date] )
            && [SalesMAX]
                > CALCULATE ( [SalesMAX], VALUES ( DimDate[Date] ) )
    )
)

The first measure:

1. Filters DimDate to dates that are earlier than the current filter context and whose SalesMAX value is greater than that in the current filter context.
2. Calculates the last date in the filtered DimDate table.

The second measure:

1. Filters DimDate as above.
2. Calculates SalesMAX on the last date in the filtered DimDate table.

I've just used [SalesMAX] in all of the measures as it is equivalent to SUM(Sales[Sales]) for a single product on a single day. Might need to be modified slightly if you wanted to apply the measure for aggregations of products, or if you have multiple rows of sales for a product on a given date (?)

I'm sure there are other ways of doing this, possibly using LASTNONBLANK on a filtered date column, and there could be another way of restoring the Date context (the last CALCULATE in each measure).

Hope that helps

 

[ImkeF]

Of course it does - thank you so much !!

(I owe you a PQ one now )

 

[ImkeF]

Hi Owen,
I'd really like to understand why this works, so do you mind if I ask another question here?:

1) How would one expect that a dimension table can be filtered by a measure (that pulls the values from a fact-table)? I always thought that tables are filtered by their columns - what is that measure filter and why does it work??

2) What does the filter-expression here do?: CALCULATE ( [SalesMAX], VALUES ( DimDate[Date] ) In my eyes, it doesn't filter anything but just removes duplicates in the Date-column. Why does that matter?

Thanks a ton,
Imke

 

[Ozeroth]

Sure Imke

I put a bit of detail here so apologies if it's a bit longwinded

1. First getting back to basics:
   The DAX FILTER function takes arguments FILTER(
   ,[filter]<filter>), </filter>
   <filter>where [</filter> <filter>filter] </filter> <filter><filter>is a boolean expression, i.e. any expression evaluating to TRUE or FALSE.</filter>
   
   </filter> <filter></filter> The FILTER function iterates over the rows of and evaluates <filter> in the row context of each row of

   </filter>, keeping those rows where [filter] <filter> = TRUE.
   <filter>[filter] is allowed to contain references not only to columns of

   </filter>

   </filter>, but also measures, other tables, or basically anything that can be evaluated in the row context of

   .
   So, for example, a call to FILTER like FILTER( DimDate, [SalesAmt] >= 1000 ) is valid, and would return the rows of DimDate for which the [Sales] measure is at least 1000. Measures/CALCULATE
   One thing I've glossed over here is how the measures or the CALCULATE function behave in a row context.
   In short, a call to CALCULATE(...) within a row context automatically converts the row context to filter context (this is called context transition), and the first argument of CALCULATE will be evaluated in that new filter context (plus the context created by any other filter arguments provided to CALCULATE). The importance of context transition is that the new filter context now means any relationships in the model take effect (which doesn't happen in row context alone).
   
   Another quirk of DAX is that any measure is automatically wrapped in an implied CALCULATE. So if SalesAmt := SUM( Sales[Sales] ), then any reference to [SalesAmt] is equivalent to CALCULATE( SUM (Sales[Sales) ).
   
   This means, in the case of FILTER( DimDate, [SalesAmt] >= 1000 ), for each row of DimDate, the expression [SalesAmt] >=1000 (i.e. CALCULATE( SUM (Sales[Sales) ) >= 1000) is evaluated. Due to context transition, the 'current' row of DimDate becomes filter context, and [SalesAmt] is evaluated taking into account the relationship from Sales to DimDate.
   
   However, the expression FILTER( DimDate, SUM( Sales[Sales] ) >= 1000) would have a different effect. Without using a measure or CALCULATE, SUM( Sales[Sales] ) would just be evaluated in the current filter context (before FILTER was called) and would produce the same result (whether TRUE or FALSE) for every row of DimDate, with the current row of DimDate making no difference to the calculation.
   
   In the example in your original post, part of the <filter> argument was </filter> [SalesMAX] > CALCULATE ( [SalesMAX], VALUES ( DimDate[Date] ). The first reference to [SalesMax], is the value of the [SalesMax] measure evaluated in the filter context derived from the 'current' row of DimDate.
   
   

   
   [*] What is CALCULATE ( [SalesMAX], VALUES ( DimDate[Date] ) doing?
   This follows on from the context transition due to the implied CALCULATE wrapped around a measure.
   Here, I want to calculate [SalesMax] but not for the current row of DimDate, but instead within the outer filter context (before FILTER was called).
   Normally, the context transition caused by the measure [SalesMax] would force me to evaluate in the filter context derived from the current row of DimDate (not what I want here!). However, I can override this by putting [SalesMax] within another CALCULATE which used VALUES ( DimDate[Date] ) as a setfilter argument.
   VALUES ( DimDate[Date] ) refers to the values of [Date] in the filter context before FILTER was called (regardless of current row of DimDate), so it 'restores' the original filter context. This is useful since I can compare the 'current DimDate row's' [SalesMax] to the current filter context [SalesMax].

 

[ImkeF]

Wow, that's worth more than a ton of thanks - make it 2 then

Very, very helpful: This makes such a difference, as it wouldn't have crossed my mind that I can basically filter my long fact table on the amount without having to iterate through it. Which of course destroys any performance. Sounds as if this could be useful in other areas as well.

Re 2: Unfortunately this context-transition doesn't fit into my RAM. Actually I tried sth with EARLIER, but it throw an error saying that there wasn't any row context (but cannot remember exactly how I did it). But luckily we have variables by now...

So again, thxxxx - Imke

 

[Ozeroth]

No problem

And yes, these days I would usually use variables rather than trying to restore the outer filter context or earlier row context.

Cheers,
Owen