Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Create column with DAX which will provide latest data available

I want to create a column in power bi desktop which will show case only latest data available.

 

MonthYearValue
Jan202467
Feb202423
Mar202444
Apr2024 
May2024

 

 

i have data like this and i want to create a logic in such a way so that it will fethch whatever latest months data available in the column.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, Anonymous 

    You can refer to amitchandak reply. If it does not help, you can try this.

    Data available within five months =
VAR _lastestMonth = 5
VAR _availableDate =
    EOMONTH ( TODAY (), -6 ) + 1
VAR _flag =
    IF (
        SELECTEDVALUE ( 'Table'[Date] ) >= _availableDate
            && SELECTEDVALUE ( 'Table'[Value] ) <> BLANK (),
        1,
        0
    )
RETURN
    _flag

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Anonymous , In Power query you can use fill down

     

    Power Query - Fill Up Fill Down: https://youtu.be/mC2ps0pFqBI

     

    In DAX a new column, First have date column

    Date = datevalue( "01-"& [Month] & "-" &[Year])

    Now create a measure like

    New column =

    var _max = maxx(filter(Table, [Date]< earlier([Date]) && not(isblank([Value]))) , [Date])

    return
    If(isblank([Value]) , Max(Filter(Table, Table[Date] =_max), [Value]) , [Value])

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Anonymous 

    You can refer to amitchandak reply. If it does not help, you can try this.

    Data available within five months =
VAR _lastestMonth = 5
VAR _availableDate =
    EOMONTH ( TODAY (), -6 ) + 1
VAR _flag =
    IF (
        SELECTEDVALUE ( 'Table'[Date] ) >= _availableDate
            && SELECTEDVALUE ( 'Table'[Value] ) <> BLANK (),
        1,
        0
    )
RETURN
    _flag

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum