DAX

[leejq1]

Hello, I need to create a measure to get the last month's latest date Odometer reading as Opening ODO (measure name). This is the current formula I have and it works well when my filter selection is one month, but if I selected two months in my filter this formula would not return any values. Can anyone help me with it?

Opening ODO =

VAR CurrentIndex = SELECTEDVALUE('Calendar table'[Index])

VAR LastMonthIndex = CurrentIndex - 1

VAR LatestDateInLastMonth =

    CALCULATE(

        MAX('Shell Report'[Date]),

        FILTER(

            ALLSELECTED('Calendar table'),

            'Calendar table'[Index] = LastMonthIndex

        ),

        FILTER(

            ALL('Shell Report'),

            'Shell Report'[Vehicle License Number] = MAX('Shell Report'[Vehicle License Number])

    )

    )

VAR OdometerReadingOnLatestDate =

    CALCULATE(

       MAX('Shell Report'[Odometer Reading]),

       'Shell Report'[Date] = LatestDateInLastMonth,

        FILTER(

            ALL('Shell Report'),

            'Shell Report'[Vehicle License Number] = MAX('Shell Report'[Vehicle License Number])

        )

    )

RETURN

    OdometerReadingOnLatestDate

[Poojara_D12 · Accepted solution]

Hi @leejq1 
I have two solutions for it, please try and let me know if it helped you in any way. If MAX is not working for you, you can try the below given methods:

1. DAX

Opening ODO =
VAR CurrentMonthIndex = MAXX(ALLSELECTED('Calendar table'), 'Calendar table'[Index])
VAR LastMonthIndex = CurrentMonthIndex - 1

VAR LatestDateInLastMonth =
    CALCULATE(
        LASTDATE('Shell Report'[Date]),
        FILTER(
            ALL('Calendar table'),
            'Calendar table'[Index] = LastMonthIndex
        ),
        FILTER(
            ALL('Shell Report'),
            'Shell Report'[Vehicle License Number] = MAX('Shell Report'[Vehicle License Number])
        )
    )

VAR OdometerReadingOnLatestDate =
    CALCULATE(
        MAX('Shell Report'[Odometer Reading]),
        'Shell Report'[Date] = LatestDateInLastMonth,
        FILTER(
            ALL('Shell Report'),
            'Shell Report'[Vehicle License Number] = MAX('Shell Report'[Vehicle License Number])
        )
    )

RETURN
    OdometerReadingOnLatestDate

  This should work correctly even with multiple months selected in the filter.

2. DAX

Opening ODO =
VAR CurrentMonthIndex = MAXX(ALLSELECTED('Calendar table'), 'Calendar table'[Index])
VAR LastMonthIndex = CurrentMonthIndex - 1

VAR LatestDateInLastMonth =
    CALCULATETABLE(
        TOPN(
            1,
            FILTER(
                ALL('Shell Report'),
                'Shell Report'[Vehicle License Number] = MAX('Shell Report'[Vehicle License Number])
                && RELATED('Calendar table'[Index]) = LastMonthIndex
            ),
            'Shell Report'[Date],
            DESC
        )
    )

VAR OdometerReadingOnLatestDate =
    CALCULATE(
        MAX('Shell Report'[Odometer Reading]),
        FILTER(
            LatestDateInLastMonth,
            'Shell Report'[Vehicle License Number] = MAX('Shell Report'[Vehicle License Number])
        )
    )

RETURN
    OdometerReadingOnLatestDate

This version with TOPN can be more flexible in situations where MAX or LASTDATE do not provide the intended result across multiple months. Give this a try and let me know how it goes!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

[FreemanZ]

hi @leejq1 ,

try to replace SELECTEDVALUE with MAX

[leejq1]

Thanks for youe advice. It doesnt work with MAX

[123abc]

let’s try a different approach. Instead of using MAX, we can use TOPN to get the latest date and corresponding odometer reading. Here’s an updated version of your measure:

Opening ODO =
VAR CurrentIndex = SELECTEDVALUE('Calendar table'[Index])
VAR LastMonthIndex = CurrentIndex - 1

VAR LatestDateInLastMonth =
CALCULATE(
MAX('Shell Report'[Date]),
FILTER(
ALL('Calendar table'),
'Calendar table'[Index] = LastMonthIndex
),
FILTER(
ALL('Shell Report'),
'Shell Report'[Vehicle License Number] = MAX('Shell Report'[Vehicle License Number])
)
)

VAR OdometerReadingOnLatestDate =
CALCULATE(
MAXX(
TOPN(
1,
FILTER(
ALL('Shell Report'),
'Shell Report'[Date] = LatestDateInLastMonth &&
'Shell Report'[Vehicle License Number] = MAX('Shell Report'[Vehicle License Number])
),
'Shell Report'[Date], DESC
),
'Shell Report'[Odometer Reading]
)
)

RETURN
OdometerReadingOnLatestDate

This should help in getting the correct odometer reading even when multiple months are selected. Give this a try and let me know if it works!