Max overall date in columns

[Pepe1989]

Hi everyone,

I'm strugeling to create a measure which will show max date selected by slicer excluding row context filter.

e.g. Last Trade Date

I have a reporting date dimension including Date, MonthEndDate, Year, Quarter...

And a Transactions fact with numerous measures and foreign key to Date dimension.

Data Mode:

What i would like to see is below example. So based on date slicer show last trade date in all rows.

Thanks in advance!

[Anonymous · Accepted solution]

Hi @Pepe1989 ,

You can create a measure as below, please find the details in the attachment.

Last Trade Date = 
VAR _sumofamount =
    SUM ( 'Transactions'[Amount] )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Transactions'[ReportingDateFK] ),
        FILTER (
            ALLSELECTED ( 'Transactions' ),
            'Transactions'[ReportingDateFK] >= MIN ( 'Reporting Date'[Date] )
                && 'Transactions'[ReportingDateFK] <= MAX ( 'Reporting Date'[Date] )
        )
    )
RETURN
    IF ( ISBLANK ( _sumofamount ), BLANK (), _maxdate )

Best Regards

[amitchandak]

@Pepe1989 , Try a measure like

maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])

or maxx(ALLSELECTED('Table'), 'Table'[Date]) 

of we can find

Max Date  =
var _min = minx(ALLSELECTED('Date'), 'Date'[Date])
var _max = maxx(ALLSELECTED('Date'), 'Date'[Date])
return
CALCULATE(Max(Table[Date]) ,filter(all('Date'), 'Date'[Date] >=_min && 'Date'[Date] <= _max ))

[Pepe1989]

Hi @amitchandak,

Unfortunately, because I wanted to simplify model as much as possible, I didn’t list other dimensions link to that fact table.

Now, with your code I’m getting max date but only by other dimensions.
full data model is presented below:

Thanks