cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
music43
Helper I
Helper I

DAX Noob MAX date

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.

1 ACCEPTED SOLUTION
daxer
Solution Sage
Solution Sage

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 )
)

 

View solution in original post

2 REPLIES 2
daxer
Solution Sage
Solution Sage

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 )
)

 

View solution in original post

amitchandak
Super User IV
Super User IV

@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] )) )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors