Measure that calculates a column depending on date filter

[Fátima]

Hi!

I have a table similar to this:

TABLE1

ID   CENTER_ID   DATE

1     1                  12/03/2022

2     1                  13/03/2022

3     2                  12/03/2022

4     3                  12/03/2022

5     3                  13/03/2022

I needed to check which are the "active" records of each center, these would be the ones with the latest date, so I created a calculated column like this:

DAX:

isActive=

var _filter=FILTER(TABLA1,[CENTER_ID]=EARLIER('TABLA1'[CENTER_ID]))

return if([DATE]=MAXX(_filter,[DATE]),TRUE(),FALSE())

It gives me this result:

TABLE1

ID   CENTER_ID   DATE               isActive

1     1                  12/03/2022     FALSE

2     1                  14/03/2022     TRUE

3     2                  11/03/2022     TRUE

4     3                  12/03/2022     FALSE

5     3                  13/03/2022     TRUE

The problem I'm having is that I need it to be filtered on my report by a date filter. So if for example I set the filter to the records that have been created before 12/03/2022, I get:

TABLE1

ID   CENTER_ID   DATE               isActive

1     1                  12/03/2022     FALSE

3     2                  11/03/2022     TRUE

4     3                  12/03/2022     FALSE

But I would like to get:

TABLE1

ID   CENTER_ID   DATE               isActive

1     1                  12/03/2022     TRUE

3     2                  11/03/2022     TRUE

4     3                  12/03/2022     TRUE

Because those records where the active ones on that date.

How could I do this? With a measure, I guess?

Thanks, regards!

[amitchandak · Accepted solution]

@Fátima , Try this measure for latest record

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[Fátima]

Thank you for the tutorials @amitchandak ! I got what I needed!

If you don't mind, I have another question related to this.

I created both measures, so now I can show how many active forms are on a specific date.

Is it possible to filter a chart by the first measure? The one that shows the last record of each CENTER_ID in my case? I'll tell you an example. I have this bar chart that shows results taken from other table, but only based on the active records. Before, when I had the calculated column (isActive), I had a filter in the filter section with isActive=TRUE checked.

How can I filter them now, with this new measure?

Regards!