Forum Discussion

Pepe1989's avatar
Pepe1989
New Member
4 years ago
Solved

Max overall date in columns

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!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Pepe1989
      New Member
      4 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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