DAX Noob MAX date

[music43]

Hi all

I just want to answer some basic questions and principles based around a max date.

A) Max date of last order based on date selection

B) Max date of last order regardless of any selection

My model contains a sales table linked to the date table via a date field. The date table has dates to the end of the year.

1) Should I reference the dates[date] field where possible instead of the sales[date] field?

2) I see that MAX( date[date] ) seems to work to answer (A) but, should I be more specific doing something like CALCULATE( MAX( date[date]), ALLSELECTED( date[date] ) )?

3) As my date table extends to the end of the year, I assume I have to reference the sales[date] field to answer (B) and should I do something like CALCULATE( MAX( date[date]), ALL( date[date] ) )?

Thanks in advance.

[Anonymous · Accepted solution]

1) Should I reference the dates[date] field where possible instead of the sales[date] field?

> The rule is this: all fields in fact tables MUST ALWAYS BE HIDDEN from the end user. They should only be able to select attributes from dimensions. In your measures you are free to use any fields, though. However, you will know that filters will only be present on dimensions (which is very useful and the only correct way to do things with one exception---degenerate dimensions). If you do time-intel calcs, you should always reference the date/time dimension table, but if you need to calculate something that is calculatable from the date field in the fact table, you are free to do it.

2) I see that MAX( date[date] ) seems to work to answer (A) but, should I be more specific doing something like CALCULATE( MAX( date[date]), ALLSELECTED( date[date] ) )?

> Since not all dates in the date dimension must necessarily have an order associated with them, you should calculate the max date of the last order using the field in the fact table, not the dimension, even though by using two-way filtering (enabled in the measure) you can also do it using the dimension but this isn't the optimal and fastest way. ALLSELECTED is the most complex function in all DAX and whether you need to use it or not depends on what you want to achieve. It's mostly used to calculate the so-called "visual totals." (but it's not everything it's useful in). Since, as I said above, you should never use a fact table's columns to slice and dice, using the field Dates[Date] as the parameter of the function is correct.

3) As my date table extends to the end of the year, I assume I have to reference the sales[date] field to answer (B) and should I do something like CALCULATE( MAX( date[date]), ALL( date[date] ) )?

> Almost correct. To calculate the very last day you had an order on, you should do:

CALCULATE(
    MAX( FactTable[Date] ),
    // This will remove all the filters
    // that affect the FactTable but only
    // if the relationships are strong
    // (not weak). If some relationships
    // are weak, you'll have to use
    // something like ALL( ) (with no
    // parameters inside the parens).
    REMOVEFILTERS( FactTable )
)

[amitchandak]

@music43 , if you want to keep sales reference better take max of sales date in both cases. In case of all selected the filter may work but in case all it will give a max of date table

CALCULATE( MAX( Sale[date]), ALL( Sale[date] ) )

or try

 CALCULATE( MAX( date[date]), ALL( date[date] ), not(isblank(Sale[date] )) )