Return most recent data as of a selected date

[jmoedata]

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!

[v-kaiyue-msft]

Hi @jmoedata ,

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

1. Create a date table.

DAX DateTable = 
ADDCOLUMNS (
    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.

If your Current Period does not refer to this, please clarify in a follow-up reply.

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.

[jmoedata]

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?

[v-kaiyue-msft]

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.

[jmoedata]

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.

[v-kaiyue-msft]

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.

[jmoedata]

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!

[v-kaiyue-msft · Accepted solution]

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.

[jmoedata]

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

[v-kaiyue-msft]

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.

[jmoedata]

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

[VN999]

Refer this below link existing post that might help you solve this requirement:

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