Get latest values for N last days using DirectQuery

[andriikubrak]

I have a table that has Date column and Value column. I am using SQL Server with DirectQuery mode. I need to build a chart that has latest values for each of 2 last days. Should look like here:

Here is a table:

To achieve this I decided to create two calculated columns for X (days) and Y (values) axes. But I encountered issues when functions like MAX (to get most recent date) or FORMAT (to format date to "Fri" or "Sat") are not supported using DirectQuery in calculated columns. Does somebody have an idea how can I solve it?

[v-jiascu-msft]

Hi @andriikubrak,

1. How to add dates like "Fri"?

1) If you can add a week column in the database, you can try to add a new column in the PBI.

Week =
LEFT ( [Day of Week], 3 )

2) If you can't, I wound if you can accept the format like this: "Sunday 1 January 2017".

2. About Max. Does the second max date have some features? For example, it's the max date minus 1. 

Because "Function 'FILTER' is not supported in this context in DirectQuery mode". We can't use filter to get the second max date. Or you have to do it in the database.

Best Regards!

Dale