show value based on current date

[Anonymous]

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

[Anonymous]

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]

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 · Accepted solution]

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]

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

Thank you

[amitchandak]

@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]

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

[Anonymous]

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]

@Anonymous 

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

Aiolos Zhao

[Anonymous]

it shows (Blank)