MAXDate returns max date of dimension not FACT table (DAX)

[CLEARIFY]

FACT table with link to Date Dimension in star schema fashion.


Date Dimension holds 20 years of dates, linked to fact table by Integer primary and foreign key fields.


Have designated date dimension as 'date table' in model, with transaction date being the 'date field' (And tried without).


I have tried various forms of Max Date or Last Date to get the latest date of a transaction in the fact table, but I always get the last date in the date dimension table
Simple would be: =MAX('Calendar - Transaction Date'[Transaction Date])

Using Max Date in a regular pivot table produces the correct results:

Using Max Date in a PowerPivot produces different results:

Lastly bringing in the maxdate in a powerpivot table removes the filters I have set for the powerpivot report.

 

[MD610]

You need a little bit more filtering on your formula to make it work as a Calculated Column in your Fact table.

You should read up on filter context vs. row context. Many DAX formulas will behave differently when used as a Measure vs. when used as a Calculated Column.

To make your formula work as a Calculated Column, you must wrap it in a CALCULATE() with an additional filter that checks for earlier occurrences of the product on the fact table:

=CALCULATE(
                         MAX('Calendar - Transaction Date'[Transaction Date]),
                         FILTER(
                              'FACTS',
                              'FACTS'[Product Name or ID] = EARLIER('FACTS'[Product Name or ID])
                         )
               )

If your FACT table is really big, this will take a while to process and you might be better off just leaving MaxDate as a measure and not trying to make a Calculated Column out of it.

 

[CLEARIFY]

You need a little bit more filtering on your formula to make it work as a Calculated Column in your Fact table.

You should read up on filter context vs. row context. Many DAX formulas will behave differently when used as a Measure vs. when used as a Calculated Column.

To make your formula work as a Calculated Column, you must wrap it in a CALCULATE() with an additional filter that checks for earlier occurrences of the product on the fact table:

=CALCULATE(
                         MAX('Calendar - Transaction Date'[Transaction Date]),
                         FILTER(
                              'FACTS',
                              'FACTS'[Product Name or ID] = EARLIER('FACTS'[Product Name or ID])
                         )
               )

If your FACT table is really big, this will take a while to process and you might be better off just leaving MaxDate as a measure and not trying to make a Calculated Column out of it.

I received some help from a Marco Russo in another forum, but it relies on the fact that I created a RELATED column in the FACT table to have a date; There are issues with the date dimension that I am still investigating - its behavior is odd; but for a later day. I will share the results here of what I got to work:

For maximum date for any given row label context: CALCULATE ( MAX ( 'FactTable'[Related Date Field Column] ), FactTable )

For maximum date for the whole table regardless of context: CALCULATE ( MAX ( 'FactTable'[Related Date Field Column] ), FILTER ( ALL ( FactTable ), TRUE ) )