Forum Discussion

Lakshmi123's avatar
Lakshmi123
New Member
1 year ago
Solved

Get an Latest date Records

Hi ,

i created below table by using 3 tables : 

dim TableA- state

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

dim TableC- date_dt

 

statenumdiffdate_dt
alabama100010020-Feb-25
alaska20001027-Feb-25
alaska220020027-Mar-25
arizona150010022-Feb-25
arizona1000-50027-Mar-25
arizona2000100020-Apr-25
california300010022-Jan-25
california4000100022-Feb-25
california1000-300027-Mar-25
california5000400020-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:

statenumdiffdate_dt
alabama100010020-Feb-25
alaska220020027-Mar-25
arizona2000100020-Apr-25
california5000400020-Apr-25

 

Thank you in advance.

Lakshmi

Filter
Group By
Latest Date
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    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 )
        )
    )

     

5 Replies

  • shafiz_p's avatar
    shafiz_p
    Icon for Super User rankSuper User
    1 year ago

    Hi Lakshmi123  Try this measure:

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

     

    Hope this helps!!

     

    Thanks,

     

    Shahariar

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    1 year ago

    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 )
        )
    )

     

  • v-sgandrathi's avatar
    v-sgandrathi
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
      v-sgandrathi
      Icon for Community Support rankCommunity Support
      1 year ago

      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's avatar
        v-sgandrathi
        Icon for Community Support rankCommunity Support
        1 year ago

        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.