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

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

        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

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

      Anonymous , does you date has a timestamp in it , if so create a date column

       

      Date = [Datetime].date

       

      then join it with date table and filter for today.

       

      calculate(sum(Table[value]), filter(Date, Date[Date]=Today()))

       

      Make sure you have data for today

  • 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