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   state num diff date_dt alabama 1000 100 20-F...
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 )
        )
    )

     

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