topic Re: DAX in DAX Commands and Tips

DAX

leejq1 — Thu, 14 Nov 2024 03:22:52 GMT

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

Re: DAX

FreemanZ — Thu, 14 Nov 2024 03:45:16 GMT

hi leejq1 ,

try to replace SELECTEDVALUE with MAX

Re: DAX

leejq1 — Thu, 14 Nov 2024 04:10:02 GMT

Thanks for youe advice. It doesnt work with MAX

Re: DAX

Poojara_D12 — Thu, 14 Nov 2024 05:39:32 GMT

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

Re: DAX

123abc — Thu, 14 Nov 2024 08:21:10 GMT

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!