Get the latest value (also group by slicer filter)

[Maru]

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 ?

[Maru]

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

[LufengYuan]

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?

[Maru]

Unfortuntely not.

[Fowmy]

@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

[Maru]

Total has to be 14

[Fowmy]

@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 

[Maru]

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

[Fowmy]

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

[Maru]

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

[LufengYuan]

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

[Maru]

Would it work if you select 'Jan' ?