MAX date

[JolienR]

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:

[Anonymous · Accepted solution]

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

[Bmejia]

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])

[Bmejia]

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

[JolienR]

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..

[Anonymous]

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.

[Anonymous]

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.