Need to get the MAX date from a date dimension

[vpoulson]

I'm trying to figure out how to get the MAX date from a date dimension table.  I have three tabels: a fact table that has the datekey and locationkey, date dimension that has the datekey and the date and a location table that has the locationkey and location name.  For each location name I need to get the last date there is an entry in the fact table.  

For this data:

I want these results

Here are screenshots of the tables:

Locations

DimDates

MyValues fact table

If needed I can share the pbix file too.

[qqqqqwwwweeerrr · Accepted solution]

Hi @vpoulson 

If you want to create a new column then you can use this formula:
Column = CALCULATE(MAX(table_1[Date]),FILTER('table_1',table_1[Location] = EARLIER(table_1[Location])))
If you want to create measure you can use this measure:

maxenddate = CALCULATE(MAX(table_1[EndDate]),REMOVEFILTERS(table_1[EndDate]))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: https://www.youtube.com/@letssolveproblem

Regards

[qqqqqwwwweeerrr · Accepted solution]

Hi @vpoulson 

If you want to create a new column then you can use this formula:
Column = CALCULATE(MAX(table_1[Date]),FILTER('table_1',table_1[Location] = EARLIER(table_1[Location])))
If you want to create measure you can use this measure:

maxenddate = CALCULATE(MAX(table_1[EndDate]),REMOVEFILTERS(table_1[EndDate]))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: https://www.youtube.com/@letssolveproblem

Regards

[vpoulson]

Thanks for the help.  For this simple data structure both your suggestions work.  The actual model I'm working with is a bit more complex.  The date and location dimensions are linked to several fact tables.  Also the fact table has several other categories that I need to filter by.  

If for example if have another fact table that also has a datekey and locationkey is there a way to tell which fact table I want the max date for?

What if my model is like this?

[v-yiruan-msft · Accepted solution]

@qqqqqwwwweeerrr  Thanks for your contribution on this thread.

Hi @vpoulson ,

@qqqqqwwwweeerrr have provide a good suggestion. You can create different measures as below to get the max date from the dimension date table base on different fact tables, please find the details in the attachment.

MyMaxDate = 
VAR _locationkey =
    SELECTEDVALUE ( 'Locations'[LocationKey] )
VAR _maxdkey =
    CALCULATE (
        MAX ( 'MyValues'[DateKey] ),
        FILTER ( ALLSELECTED ( 'MyValues' ), 'MyValues'[LocationKey] = _locationkey )
    )
RETURN
    CALCULATE (
        MAX ( 'DimDates'[Date] ),
        FILTER ( 'DimDates', 'DimDates'[DateKey] = _maxdkey )
    )

TF2MaxDate = 
VAR _locationkey =
    SELECTEDVALUE ( 'Locations'[LocationKey] )
VAR _maxdkey =
    CALCULATE (
        MAX ( 'FactTable2'[DateKey] ),
        FILTER (
            ALLSELECTED ( 'FactTable2' ),
            'FactTable2'[LocationKey] = _locationkey
        )
    )
RETURN
    CALCULATE (
        MAX ( 'DimDates'[Date] ),
        FILTER ( 'DimDates', 'DimDates'[DateKey] = _maxdkey )
    )

Best Regards