topic Re: Last estimate of latest date filtered by another colum in DAX Commands and Tips

Last estimate of latest date filtered by another colum

Anonymous — Thu, 21 Nov 2024 15:38:41 GMT

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

)

Re: Last estimate of latest date filtered by another colum

tamerj1 — Thu, 21 Nov 2024 18:24:47 GMT

Hi Anonymous

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

Re: Last estimate of latest date filtered by another colum

FreemanZ — Thu, 21 Nov 2024 21:07:52 GMT

hi Anonymous ,

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

Re: Last estimate of latest date filtered by another colum

FreemanZ — Thu, 21 Nov 2024 21:13:01 GMT

hi Anonymous ,

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

Re: Last estimate of latest date filtered by another colum

tamerj1 — Fri, 22 Nov 2024 08:11:42 GMT

FreemanZ

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

Re: Last estimate of latest date filtered by another colum

FreemanZ — Fri, 22 Nov 2024 08:28:27 GMT

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

Re: Last estimate of latest date filtered by another colum

ThxAlot — Fri, 22 Nov 2024 14:19:27 GMT

Re: Last estimate of latest date filtered by another colum

Anonymous — Mon, 25 Nov 2024 09:42:32 GMT

Hi Anonymous ，

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

Re: Last estimate of latest date filtered by another colum

Anonymous — Tue, 26 Nov 2024 19:44:56 GMT

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

Re: Last estimate of latest date filtered by another colum

Anonymous — Tue, 26 Nov 2024 19:46:13 GMT

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

Re: Last estimate of latest date filtered by another colum

Anonymous — Tue, 26 Nov 2024 19:47:50 GMT

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

Thanks anyway 🙂

Re: Last estimate of latest date filtered by another colum

Anonymous — Tue, 26 Nov 2024 19:50:24 GMT

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.

Re: Last estimate of latest date filtered by another colum

Anonymous — Tue, 26 Nov 2024 19:52:03 GMT

Thanks!

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