Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Retrun date table if values less than a given value

Please help me in setting up my table to return date values that are about 10 years less than the current date. I tried everything and still not getting the hopeful result. Below is my query:

EntryDateColumn =
var _lookUpDate = LOOKUPVALUE(QuarantineCase[EntryDate], QuarantineCase[EntryDate], QuarantineCase[EntryYearFilter], (QuarantineCase[EntryDate].[Year] ))
return
IF(MAX(QuarantineCase[EntryDate]) > VALUE(_lookUpDate), _lookUpDate, BLANK())

Thanks.

Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , Try a new column

     

    New column =
    var _Date = date(Year([Date])-10 , month([Date]), Day([Date]))
    return
    maxx(filter(Table, Table[Date]< _Date),[Date])

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Which in your formula is the current date and which is the given date?

    In column, you could directly use column name to get the value of the current row.

    If you use MAX() function, it will return the max value of the column instead.

    For example to return the date in the date table that is equal to 10 years before the current date.

    new table = filter('date',YEAR('date')<YEAR(today())-10)

    You could also take a look at dateadd-function and edate-function .

    Please share some sample data and expected result so that we could help with DAX.

     

    Best Regards,

    Jay

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Try a new column

     

    New column =
    var _Date = date(Year([Date])-10 , month([Date]), Day([Date]))
    return
    maxx(filter(Table, Table[Date]< _Date),[Date])

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks for the response. However, I'm getting the error...
    Cannot convert value 'July' of type Text to type Date.
    I checked and the [DATE] field is defined as datetime in the databases and I also double checked the field in Power BI