Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

show value based on current date

Hello, I have a Date table with columns such as DateKey, PreviousWorkingDateKey, etc. I would like to have a measure which shows the previousWorkingDateKey for today's DateKey How is ithis done pl...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    HI  Anonymous 

    If so, just adjust the formula as below:

    Measure 2 = 
var _preworkingdatekey= calculate(max('Date'[PreviousWorkingDate]),filter('Date','Date'[DateValue] = today())) return
DATE(VALUE(LEFT(_preworkingdatekey,4)),VALUE(MID(_preworkingdatekey,5,2)),VALUE(RIGHT(_preworkingdatekey,2)))

     

    Regards,

    Lin

Anonymous's avatar
Anonymous
Not applicable
6 years ago

My previous post saying it shows (Blank) was wrong.
It actually shows 20M whereas it should show a date.
Any thoughts please?

Thank you

Anonymous's avatar
Anonymous
Not applicable
6 years ago

could you please show some sample data and the result you want?

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    sample data

     

    DateKey       DateValue        PreviousWorkingDate
    20140306 2014-03-06     20140305
    20140307 2014-03-07     20140306

     

    so if today's date is, say, 7th march 2014 then the measure should show the [date value] which represents the [previousworkingDate ]= 20140306
     

    Thank you

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous 

       

       

      Measure 6 = CALCULATE(MAX(Account[PREVIOUSWORKING]),Account[DATEVALUE] = TODAY())

       

      Aiolos Zhao