cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Return most recent data as of a selected date

I am in a need of a little help with how PowerBI can help accomplish what we are trying to do.

A user would like to select a date and we would like to return only the most recent records for a person.  Here is our example

John Doe

12345, 2024-06-24, John Doe, 123 Main Street, Anytown, TX

12345, 2024-06-15, John Doe, 999 Main Street, Anytown, CA

12345, 2024-06-02, John Doe,  4567 Hills Drive, Anytown, TX

Jane Smith

99993, 2024-06-28, Jane Smith, 123 Main Street, Anytown, TX

99993, 2024-06-07, J Doe, 999 Main Street, Anytown, CA

99993, 2024-06-05, Jane Doe,  4567 Hills Drive, Anytown, TX

In our example, of the user selects a date = 2024-06-14, then we would want row 1 from John Doe and row 2 from Jane Smith.

Is there a way that PowerBI can accomplish this?

Thanks!

1 ACCEPTED SOLUTION
Community Support

Hi @jmoedata ,

You can create new MEASURES to count against previous MEASURES.

``````Measure 2 =
COUNTX(FILTER('Table',[Measure] = 1),[Measure])``````

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

11 REPLIES 11
Community Support

Hi @jmoedata ,

Thanks for the reply from @VN999 , please allow me to provide another insight:

1. Create a date table.

``````DAX DateTable =
CALENDAR(DATE(2024,6,1), DATE(2024,6,30)),
// CALENDARAUTO(),
"Year", YEAR([Date]),
"Quarter", "Q" & FORMAT(CEILING(MONTH([Date])/3, 1), "#"),
"Quarter No", CEILING(MONTH([Date])/3, 1),
"Month No", MONTH([Date]),
"Month Name", FORMAT([Date], "MMMM"),
"Month Short Name", FORMAT([Date], "MMM"),
"Month Short Name Plus Year", FORMAT([Date], "MMM,yy"),
"DateSort", FORMAT([Date], "yyyyMMdd"),
"Day Name", FORMAT([Date], "dddd"),
"Details", FORMAT([Date], "dd-MMM-yyyy"),
"Day Number", DAY ( [Date] )
)``````

2. Create a measure.

``````MEASURE =
VAR _date =
CALCULATE (
MAX ( 'Jane Smith'[date] ),
FILTER (
ALL ( 'Jane Smith' ),
'Jane Smith'[date] <= MAX ( 'DAX DateTable'[Date] )
)
)
RETURN
IF ( MAX ( 'Jane Smith'[date] ) = _date, 1, 0 )``````

3. Filter the data with a measure value of 1.

The same operation is performed on the other table. I will not go into details here. For details, please see the attachment.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Thanks you. I believe that this is close to what I am looking for. If the data is all in the same table, and we want to aggregate to find the count of people, this doesnt seem to be working.  Any thoughts?

Community Support

Hi @jmoedata ,

According to your description, I modified the data.

Create measure.

``````Measure =
VAR _max_date = CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[name] = MAX('Table'[name]) && 'Table'[date] <= MAX('DAX DateTable'[Date])))
RETURN
IF(MAX('Table'[date]) = _max_date,1,0)``````

Filter the value of measure equal to 1.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Thanks for your help on this, but I am not able to get this to work.  When I try to filter the rows where Measure = 1, I can get the individual rows, but if I try to do any aggregation such as count, my result set returns nothing.  Is there a way to filter the rows to current row based on the date selected and do the aggregation? Thank you.

Community Support

Hi @jmoedata ,

Sorry, I don't quite understand your new requirement. Can you use screenshots or virtual tables to show your expected results?

In addition, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Here is a screenshot of what I am trying to do with the different examples of the data.  Please let me know if you have ideas based on comments in the screenshot.  (I tried to upload the .pbix file, but it is not supported)

Thanks!

Community Support

Hi @jmoedata ,

You can create new MEASURES to count against previous MEASURES.

``````Measure 2 =
COUNTX(FILTER('Table',[Measure] = 1),[Measure])``````

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Thanks, but why would mine show count = 6 when I use that same formula?

Community Support

Hi @jmoedata ,

Can you share your pbix file (after removing sensitive data)? So that I can help you to see what the problem is.

Also, you can compare yourself with the attachment, for example if you have multiple rows of duplicate data in your source data, etc.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

I was able to get this to work as your solution was correct! Thank you for your help!

Resolver I

Solved: DAX formula to return most recent cost for SKU - Microsoft Fabric Community

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors