Last estimate of latest date filtered by another colum

[Armas]

Hi. 

I have a dataset where users report on the same estimate over and over again. 

I need my rapport to only show the latest estimate. 

So they will report estimated sales for July in April, May and June, resulting in three different estimates for July. 

I then want to filter out the latest estimate, for each user, for each month. As shown in the table below (Last est sales)

As this: 

I managed to find a measure that fiters by month, but i need it to also filter by user. 

Last est sales = calculate(

    LASTNONBLANKVALUE('Table'[Reporting date],

     max('Table'[Est sales])),

     filter(

        ALLSELECTED('Table'),

        'Table'[Month] = max('Table'[Month])

         )

         )

[tamerj1 · Accepted solution]

Hi @Armas 

Please try

Last est sales =
SUMX (
Vales ( 'Table'[User] ),
SUMX (
TOPN (
1,
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[User] = EARLIER ( 'Table'[User] ) ),
'Table'[Month]
),
'Table'[Est sales]
)
)

[Armas]

Thanks! A new table was the best solution for me. This worked great 🙂

[FreemanZ]

hi @Armas ,

try to add a calculated column like:

column=

VAR _lastdate =

CALCULATE(

MAX(data[reporting date]),

ALLEXCEPT(data, data[user], data[month])

)

RETURN

IF(data[reporting date] = _lastdate, data[Est sales], BLANK())

[Armas]

Thanks! This worked, but the table-solution was easier for continued use of the data. 

[FreemanZ]

hi @Armas ,

if the screenshot is a visual, try like:

measure =

VAR _lastdate =

CALCULATE(

MAX(data[reporting date]),

ALLEXCEPT(data, data[user], data[month])

)

RETURN

IF(MAX(data[reporting date]) = _lastdate, SUM(data[Est sales]), BLANK())

[tamerj1]

@FreemanZ 

The column would work perfectly but the measure would produce wrong total. 

[FreemanZ]

yes, indeed SUMX(VALUES(), ) is much safer and more robust to ensure a consistent total. 

[Armas]

Couldn't get this to work properly, and there was another reply with the new table that worked perfectly. 

Thanks anyway 🙂

[ThxAlot]

[Armas]

Thanks! 

I will test this as well, but everything worked great with the table-solution from tamerj1

[Anonymous]

Hi @Armas ，

How is the situation now? If the problem has been solved, please accept the answers you find helpful as solutions.

You can also try this.

VAR filtered = FILTER(ALLSELECTED('Table'),'Table'[Month]=MAX('Table'[Month])&&'Table'[User]=MAX('Table'[User]))
VAR last_date = MAXX(filtered,'Table'[Reporting date])
RETURN IF(MAX('Table'[Reporting date])=last_date,MAX('Table'[est sales]),"")

Best regards,

Mengmeng Li

[Armas]

Thanks for following up :). 

After trying several of the solutions, and using the results in new measures and visuales I ended up with a good solution. 

I will look into this solution as well, as it might simplyfy the report. Thanks for the reply.