cancel
Showing results for
Did you mean:
Helper IV

## How to filter table with latest day, dynamically?

Hi everyone. Still trying to understand the basics of CALCULATE and filter context.
This problem is really hard for me to solve, so looking forward to your input/solutions.

My objective is to build tables, that only include latest data for each category;

With no filters, I wan't the latest row for each category:

And if filtered on District=z, I want:

Here is a workbook with the data/tables - thanks in advance!

What I have

My current attempt is to add a measure, and then use it as visual filter:

``````IsLatest = MAXX(
'fact',
VAR Category = 'fact'[category] RETURN
VAR Latest = CALCULATE(MAX('fact'[date]), ALLSELECTED(),'fact'[category] == Category) RETURN
IF('fact'[date] == Latest, 1, 0)
)``````

But it does not return the correct value (here filtered on distrcit=z);

1 ACCEPTED SOLUTION
Super User

@alexbjorlig

You have built the correct logic but you don't need to iterate over the fact table.
I modified your measure:

``````Latest =
VAR __category = MAX('fact'[category])
VAR __maxdate = CALCULATE( MAX('fact'[date] ) , ALLSELECTED('fact' ) , 'fact'[category] = __category )
return
INT ( max('fact'[date]) = __maxdate )``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
9 REPLIES 9
Super User

@alexbjorlig

You have built the correct logic but you don't need to iterate over the fact table.
I modified your measure:

``````Latest =
VAR __category = MAX('fact'[category])
VAR __maxdate = CALCULATE( MAX('fact'[date] ) , ALLSELECTED('fact' ) , 'fact'[category] = __category )
return
INT ( max('fact'[date]) = __maxdate )``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper IV

Thanks man - amazing when the solution turn out to be more simple than expected 🚀

New Member

Helper IV

More information - what whould you like to know more?

As described the post, the file is here.

New Member

Okay I will check and get back to you.

New Member
First you need to create a calculated Colun in the fact table with this.
Max Y/N =
var __seldate = 'fact'[date]
var __selcate = 'fact'[category]
var __maxdate =
CALCULATE(
MAX('fact'[date]),
FILTER(
'fact',
'fact'[category] = __selcate
))
return
IF(__seldate>=__maxdate,"N","Y")

Then use this measure

Thank you.

Latest Data =
var __selsubject =
SELECTEDVALUE('fact'[subject])

return
CONCATENATEX(
FILTER(
'fact',
'fact'[Max Y/N] = "N"
),'fact'[subject])
Helper IV

Hi @KasunManchanaya - thanks for giving it a try, but does not seem to work.

I don't think it's possible to use a calculated column, because the value is only calculated once, and does not dynamically adopt.

New Member

If there is a filter requirement CC will not help you. then you have to do it in the measure itself. I will give it a try and let you know.

New Member

Mark and solved if this solve your query.