Fetch previous day's latest record for the current Date

[Anonymous]

Hi,

Below is the source data (SQL) for my requirement.

1. How to get milli-seconds from CreatedDate column when creating another calculated column?

2. I have a Date filter (CreatedDate Column from the above source) in Power BI. When i select the date vaue in the filter, the visual in PBI should display the previous day's latest record.

Thanks in advance and will be appreciated.

Regards,

Pradeep

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

1. You could get the milli-seconds from CreatedDate column with spliting columns in query edior like below.

Then you could change the data type of CreatedDate column to datetime and Close&&Apply.

2. For your output, you need create a calendar table which has no relationship with your original table.

Then you could create the measure like below.

showprevious'day_last record =
VAR a =
    MAX ( 'Table1'[CreatedDate] )
VAR select_date =
    SELECTEDVALUE ( 'Table'[Date] )
RETURN
    IF ( DAY ( a ) = DAY ( select_date ) - 1, 1, 0 )

Then you could create the date slicer with the date column in your calendar table.

Here is the output.

For more details, please refer to my attachement.

If you still need help, please share your desired output so that we could help further on it.

Best Regards,

Cherry