Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Alex_MM
Frequent Visitor

DAX measure to get most recent value

Hi.
First time I'm not able to solve my issue with this forum 😞

To summarize, I have a table with 3 columns : Date, Value, Modified
I can have multiple values for the same date.
I have already a measure to get the max value related to Date matched to an external calendar :

 

 

calculate(MAX('Table'[Value]),  Filter ('Table',  'Table'[Date] >= calculate(min('Calendar'[Date])) && 'Table'[Date] <= calculate(max('Calendar'[Date])) 

 

 

This works fine, but I need to make chang to get the most recent Value (based on Modified column).
I spent hours without success trying to add something in filter like

 

 

&& 'Table'[Modified]=MAX('Table'[Modified])

 

 

 but it seems not taken ina ccount my calendar in this case 😞

Thanks for your help or advices....

1 ACCEPTED SOLUTION
v-linyulu-msft
Community Support
Community Support

Hi, @Alex_MM 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

vlinyulumsft_0-1730350134019.png

vlinyulumsft_1-1730350134021.png

2. Below are the measure I've created for your needs:

lastt = 
VAR MAXM =
    CALCULATE ( MAX ( 'Table'[Modified] ), ALLSELECTED ( 'Table' ) )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Date] >= MIN ( 'Calendar'[Date] )
                && 'Table'[Date] <= MAX ( 'Calendar'[Date] )
                && 'Table'[Modified] = MAXM
        )
    )

3.Here's my final result, which I hope meets your requirements.

vlinyulumsft_2-1730350173326.png

 

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

 

When uploading a file, please be careful to delete sensitive information.

 

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

 

Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

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

 

View solution in original post

3 REPLIES 3
Alex_MM
Frequent Visitor

That works great in your sample aligned with my need ...
Still some issue to implement in my whole formula a bit more complicated
Small question : why keeping the 

MAX ( 'Table'[Value] ),

 as goal is to have only one value ??

Thanks for your time 👍

Hi, @Alex_MM 

Thank you for your prompt reply; I’m pleased to hear of your interest in this area.

 

Firstly, my understanding is that I retained the CALCULATE(MAX('Table'[Value])) because I believe this is part of your requirement. However, if that’s not the case, I would recommend replacing it with the SUM() function. If the content in the FILTER() remains unchanged, whether you use SUM(), MAX(), or MIN() in a measure, they will return the current row value. The difference is primarily in the total section, which explains why your previous attempts to add new filter conditions were ineffective.

vlinyulumsft_4-1730428877269.png

 This is related to how these measures depend on context, and SUM() has stricter requirements: it necessitates that the data type is numeric.
 

vlinyulumsft_5-1730428877270.png

For further details, please refer to:

SUM function (DAX) - DAX | Microsoft Learn
MIN function (DAX) - DAX | Microsoft Learn
MAX function (DAX) - DAX | Microsoft Learn
CALCULATE function (DAX) - DAX | Microsoft Learn

 

Secondly, why do I emphasise that the content in FILTER() must remain unchanged? This is because you can apply the ALL() or ALLSELECTED() functions within it, which will yield different results.

vlinyulumsft_6-1730428909336.png

 

 

For further details, please refer to:

ALL function (DAX) - DAX | Microsoft Learn
ALLSELECTED function (DAX) - DAX | Microsoft Learn
 

Next, there are calculated columns and calculated tables, which, like measures, are constructed using DAX language. However, the latter is more akin to physical tables, so you can view them in the table view.

vlinyulumsft_7-1730428909337.png

For further details, please refer to:

Measures in Power BI Desktop - Power BI | Microsoft Learn
Tutorial: Create calculated columns in Power BI Desktop - Power BI | Microsoft Learn
Using calculated tables in Power BI Desktop - Power BI | Microsoft Learn

 

Here are some posts related to similar issues that I hope will be helpful to you. I hope my explanation aids your understanding of the principles behind measures.

 Solved: Re: How to setup a visual for a table with only a ... - Microsoft Fabric Community

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

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

v-linyulu-msft
Community Support
Community Support

Hi, @Alex_MM 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

vlinyulumsft_0-1730350134019.png

vlinyulumsft_1-1730350134021.png

2. Below are the measure I've created for your needs:

lastt = 
VAR MAXM =
    CALCULATE ( MAX ( 'Table'[Modified] ), ALLSELECTED ( 'Table' ) )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Date] >= MIN ( 'Calendar'[Date] )
                && 'Table'[Date] <= MAX ( 'Calendar'[Date] )
                && 'Table'[Modified] = MAXM
        )
    )

3.Here's my final result, which I hope meets your requirements.

vlinyulumsft_2-1730350173326.png

 

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

 

When uploading a file, please be careful to delete sensitive information.

 

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

 

Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

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

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors