Get an Latest date Records

[Lakshmi123]

Hi ,

i created below table by using 3 tables : 

dim TableA- state

fact TableB- num (num of records), diff(measure)

dim TableC- date_dt

state	num	diff	date_dt
alabama	1000	100	20-Feb-25
alaska	2000	10	27-Feb-25
alaska	2200	200	27-Mar-25
arizona	1500	100	22-Feb-25
arizona	1000	-500	27-Mar-25
arizona	2000	1000	20-Apr-25
california	3000	100	22-Jan-25
california	4000	1000	22-Feb-25
california	1000	-3000	27-Mar-25
california	5000	4000	20-Apr-25

I need to generate the output shown below, where each state appears only once with its latest date  without any repetition.

Could you please guide me on how to create a measure or column in Power BI to achieve this result instead of the above output?

required output:

state	num	diff	date_dt
alabama	1000	100	20-Feb-25
alaska	2200	200	27-Mar-25
arizona	2000	1000	20-Apr-25
california	5000	4000	20-Apr-25

Thank you in advance.

Lakshmi

[v-sgandrathi]

Hi @Lakshmi123,

Thank you @Jihwan_Kim and @shafiz_p for your response.

Has your issue been resolved?If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!

[v-sgandrathi]

Hi @Lakshmi123,

As we did not get a response, may I know if the above reply could clarify your issue, or could you please help confirm if we may help you with anything else?

And if the provided information meets your requirements, you can Accept the solution and also give Kudos on that reply. It helps other users who are searching for this same information and find the information.

Your understanding and patience will be appreciated.

[v-sgandrathi]

Hi @Lakshmi123,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below, and please check the below picture and the attached pbix file.

I used INDEX dax function in the measures.

INDEX function (DAX) - DAX | Microsoft Learn

Num expected result: = 
VAR _t =
    CALCULATETABLE (
        SUMMARIZE ( 'fact', 'calendar'[Date] ),
        REMOVEFILTERS ( 'calendar'[Date] )
    )
VAR _latestdate =
    INDEX ( 1, _t, ORDERBY ( 'calendar'[Date], DESC ) )
RETURN
    IF (
        HASONEVALUE ( state[state] ),
        CALCULATE (
            SUM ( 'fact'[num] ),
            KEEPFILTERS ( 'calendar'[Date] = _latestdate )
        )
    )

Diff expected result: = 
VAR _t =
    CALCULATETABLE (
        SUMMARIZE ( 'fact', 'calendar'[Date] ),
        REMOVEFILTERS ( 'calendar'[Date] )
    )
VAR _latestdate =
    INDEX ( 1, _t, ORDERBY ( 'calendar'[Date], DESC ) )
RETURN
    IF (
        HASONEVALUE ( state[state] ),
        CALCULATE (
            SUM ( 'fact'[diff] ),
            KEEPFILTERS ( 'calendar'[Date] = _latestdate )
        )
    )

[shafiz_p]

Hi @Lakshmi123  Try this measure:

LatestDateMeasure = 
CALCULATE(
    MAX('YourTable'[date_dt]),
    ALLEXCEPT('YourTable', 'YourTable'[state])
)

Hope this helps!!

Thanks,

Shahariar