topic Re: Get the latest value (also group by slicer filter) in DAX Commands and Tips

Get the latest value (also group by slicer filter)

Maru — Mon, 15 Jan 2024 09:51:45 GMT

There are 'open order' transactions but the data sometimes not fully available daily.

So, to get a closure for a specific month, it has to be calculated from the last date (per each available group)

The group is also part of slicers. This should be easy but still I am only get it half way.

Here is an example data where country is a slicer. (let say another date slicer is in December so it should show November data)

date	product	open order	country
10/25/2023	apple	3	china
10/25/2023	apple	32	india
11/13/2023	apple	1	china
11/13/2023	apple	4	india
11/15/2023	apple	7	china
11/15/2023	apple	11	india
11/22/2023	apple	6	china
11/22/2023	apple	12	india
11/23/2023	apple	5	china
11/24/2023	apple	9	india
12/11/2023	apple	2	china
12/18/2023	apple	12	india

Current DAXs I have:

month closure = CALCULATE( sumx('Table', 'Table'[open order]),

filter('Table', 'Table'[date] = CALCULATE( max('Table'[date] ) ,

filter ( 'Table' , month('Table'[date]) = month( max('Table'[date]))-1)

)

month closure 2 =

SUMX (

SUMMARIZE (

'Table',

'Table'[country],

"@Value", SUMX ( TOPN ( 1, 'Table', 'Table'[Date] ) , 'Table'[open order] )

[@Value]

)

Am I just lose in a DAX context ?

Re: Get the latest value (also group by slicer filter)

Fowmy — Mon, 15 Jan 2024 12:07:13 GMT

Maru

Suppose your have a calendar table connected, use this meaure:

Lastest Value = Var __DatePre = EOMONTH( MAX('Calendar'[Date]), -1) Var __LastDate = CALCULATE( MAX(Table14[date]), 'Calendar'[Date]< __DatePre ) Var __Result = CALCULATE( MAX(Table14[open order]) , 'Calendar'[Date] = __LastDate) RETURN __Result

Re: Get the latest value (also group by slicer filter)

Maru — Tue, 16 Jan 2024 02:31:52 GMT

Total has to be 14

Re: Get the latest value (also group by slicer filter)

LufengYuan — Tue, 16 Jan 2024 02:41:18 GMT

Var __Result = CALCULATE( MAX(Table14[open order]) , 'Calendar'[Date] =  __LastDate)

Try to change from Max(Table14[open order]) to Sum(Table14[open order]), does it works for your total to be 14?

Re: Get the latest value (also group by slicer filter)

Maru — Tue, 16 Jan 2024 03:00:35 GMT

Unfortuntely not.

Re: Get the latest value (also group by slicer filter)

Fowmy — Tue, 16 Jan 2024 03:44:25 GMT

Maru

Try this measure:

Lastest Value = Var __DatePre = EOMONTH( MAX('Calendar'[Date]), -1) Var __Result = SUMX( VALUES( Table14[country]), Var __LastDate = CALCULATE( MAX(Table14[date]),'Calendar'[Date]< __DatePre) Var __OpenOder = CALCULATE( MAX(Table14[open order]) , 'Calendar'[Date] = __LastDate) RETURN __OpenOder ) RETURN __Result

Re: Get the latest value (also group by slicer filter)

Maru — Tue, 16 Jan 2024 06:58:43 GMT

Thank you for the update but I believe it requires either 'group by' or 'allexcept' to make it work with slicer. Fowmy

Re: Get the latest value (also group by slicer filter)

Fowmy — Tue, 16 Jan 2024 08:05:36 GMT

Maru

Sure, you may modify my solution to suit your needs. If what I shared was helpful, please go ahead and accept it as a solution.

Re: Get the latest value (also group by slicer filter)

Maru — Tue, 16 Jan 2024 11:17:15 GMT

That is what I am after and yet be able to do it so post this thread ( in case it mat not possible )

Re: Get the latest value (also group by slicer filter)

LufengYuan — Sat, 20 Jan 2024 00:27:52 GMT

Have to give credit to Fowmy. I adjusted the formula as blow. seems work. Hope it is helpful for you.

Re: Get the latest value (also group by slicer filter)

Maru — Mon, 29 Jan 2024 03:58:12 GMT

Would it work if you select 'Jan' ?

Re: Get the latest value (also group by slicer filter)

Maru — Mon, 27 Jan 2025 08:36:07 GMT

To correctly aggregate the total, I believe the only approach is to create a column that checks if a row represents the last available date (of that month) for each group of country and product (hardcoded) and then filter for rows where this column is True.

IsLastAvailableDate = VAR MaxDate = CALCULATE( MAX('Table'[Date]), ALLEXCEPT('Table', 'Table'[Country],'Table'[product], 'calendar'[Month], 'calendar'[Year]) ) RETURN IF('Table'[Date] = MaxDate, 1, 0)