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 please?
Thank you

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

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    I'm not sure your data key format, so you can change the format in the below measure to suit your type:

     

    Measure = calculate(max(perviousworkingdatekey),filter(date_table,datekey = today()))

     

    Please try.

    Aiolos Zhao

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      it shows (Blank)

    • 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

      Measure =

      = calculate(max('Date'[Previous Working Date Key]),filter('Date','Date'[Value] = today()))
      Previous Working Date key is in integers
      Date Key is in integers
      Date Value is in Date
      The above measure shows 20M rather than date.
      Do you know why?
      Thank  you
      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        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