Forum Discussion

JolienR's avatar
JolienR
Helper II
2 years ago
Solved

MAX date

How can I make sure LastOrderDate shows the max date for the selected period (=6 months)?

So, for CustomerID 188 LastOrderDate needs to show 1-9 on both rows:

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi JolienR ,

    Please try:

    LastOrderDate =
VAR __table =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'Table',
                'Table'[CustomerID],
                'Table'[ColumnName1], //2021
                'Table'[ColumnName2] //kwartaal2&kwartaal3
            ),
            "date", [OrderDates12-6Months]
        ),
        ALLSELECTED ( 'Table'[ColumnName2] )
    )
VAR _lastdate =
    MAXX ( __table, [date] )
RETURN
    _lastdate

    Output:

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Try the following:

    CALCULATE(MAX('YourTable'[LastOrderDate]), FILTER(ALL('YourTable'),

                                                                            'YourTable'[CustomerID] = SELECTEDVALUE('YourTable'[CustomerID])

                                                                            'YourTable'[OrderDate] <= MAX('YourTable'[MainDateField])

    )

     

    This effectively removes the row level filter context, then reapplies the filter context for the specified fields, in this case: CustomerID is the same as in the table; Order Date is within the range selected in the slicer. Hope this helps, let me know if you have any questions or issues.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Try the following:

    CALCULATE(MAX('YourTable'[OrderDate]), FILTER('YourTable',

                                                                        'YourTable'[CustomerID] = SELECTEDVALUE('YourTable'[CustomerID])

                                                                        && 'YourTable'[OrderDate] <= MAX('YourTable'[MainDateField]))

    )

     

    This effectively removes the filter context at row level and reapplies only some of it, in this case: CustomerID to ensure the value is specific per customerid; and OrderDate <= the largest date in your date table, so that it falls within your date slicer parameters. Hope this helps, let me know if you have any issues/questions.

  • Bmejia's avatar
    Bmejia
    Super User
    2 years ago
    LastOrderDate = CALCULATE(MAX('Table'[Orderdate]), ALLEXCEPT('Table','Table'[CustomerID]))
     
    • JolienR's avatar
      JolienR
      Helper II
      2 years ago

      Thanks for the reply. However, this gives me the max order date for the selected period (so in my case 31-10-2021). Not 1-9-2021..

  • Bmejia's avatar
    Bmejia
    Super User
    2 years ago

    I can not tell by the view what other thins are being filter, so I would suggest adding filters to exclude

     

    Column1 what ever name is the column for 2001, 35549 etc.. can continue to filter out data.

    LastOrderDate = CALCULATE(MAX('Table'[Orderdate]), ALLEXCEPT('Table','Table'[CustomerID],'Table'[Column1])

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi JolienR ,

    Please try:

    LastOrderDate =
VAR __table =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'Table',
                'Table'[CustomerID],
                'Table'[ColumnName1], //2021
                'Table'[ColumnName2] //kwartaal2&kwartaal3
            ),
            "date", [OrderDates12-6Months]
        ),
        ALLSELECTED ( 'Table'[ColumnName2] )
    )
VAR _lastdate =
    MAXX ( __table, [date] )
RETURN
    _lastdate

    Output:

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum