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

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.