Relative Date & Date Offset formula stopped working

[elliotdixon]

I have some direct query reports where some of the date functions have stopped working.

I live in New Zealand and thus have a Column added to my table called

Date NZTimeZone_NowDateTime = NOW()+(13/24)

this gives date and time and from this another column just for the date

Date NZTimeZone_NowDate = DATE(YEAR(vBI_WorkCompletedReport[Date NZTimeZone_NowDateTime]),MONTH(vBI_WorkCompletedReport[Date NZTimeZone_NowDateTime]),DAY(vBI_WorkCompletedReport[Date NZTimeZone_NowDateTime]))

These work fine. Gives me the correct date and time on the PowerBI website when the pbix file is uploaded.

 From this I have many calculations that work on relative date and I use the column

Date Relative Date = [Date DeliveryDate]-[Date NZTimeZone_NowDate]

Date DeliveryDate being a Date column and Date Relative Date being set as a whole number.

For the last year this way of calculating relative date has worked great. But in the last two weeks something has stopped working. I could look back -7 days or look in the future but now it's all stopped working. I did not update or change the reports. Gateway still working. Everything else in the reports is fine. It actually looks fine on my PowerBI desktop file on my computer.

Online I get the error

I have a future loads calculation that just looks for loads where the date is greater than 0. 0 being today. This looks fine on my computer but online I get an error.

Future Loads =CALCULATE(SUM(vBI_WorkCompletedReport[Loads]),vBI_WorkCompletedReport[JobStatus]<>13,vBI_WorkCompletedReport[Date Relative Date]>=0)

Any help with this would be greatly appreciated.

[v-shex-msft]

Hi @elliotdixon,

I test on my side, these formula works well(both desktop and service sides), Since I can't reproduce your issue, can you provide a sample file to test?

In addition, you can try to use datediff function to get the diff range.

Notice, I test on version 2.41.4581.361 64-bit (November 2016).

Regards,

Xiaoxin Sheng

[elliotdixon]

Hi @v-shex-msft I managed to get the DateDiff formula working.

Posted by @Elliott over in http://community.powerbi.com/t5/Desktop/Today-Filter-in-Reports/m-p/19886#M6012

I used

Days Aging NZ = DATEDIFF([Date],NOW()+(13/24),DAY)

as I am in New Zealand and everything has to be changed for +13 hours to get it to display right online.

Seems to work ok as a filter. Adds a column with the right relative number of days to todays date.

I can sucessfully apply this as a visual filter e.g. "Days Aging NZ is less that 7" to show the last 7 days. Works great.

However when I add this filter into a measure it all falls down. Creating something like

Last 7 Days Loads NZ 2 = CALCULATE([Loads Completed],DataTable[Days Aging NZ]<7)

does not work.

or

Last 7 Days Loads NZ = CALCULATE(SUM(DataTable[Loads]),DataTable[Days Aging NZ]<7)

or even with FILTER

Last 7 Days Loads basic = CALCULATE(SUM(DataTable[Loads]),FILTER(DataTable,DataTable[Days Aging NZ]<7))

All give errors - this sort of calculation used to work. Now get the error

seems odd

[v-shex-msft]

Hi @elliotdixon,

Since there are some limitations when you use dax on "DirectQuery" mode, I'd like to suggest you calculate these values at query editor.

Regards,

Xiaoxin Sheng

[elliotdixon]

Hi @v-shex-msft Thanks for looking into this.

Unfortunately with it being a direct query connection the pbix file wont work. Attached is an example anyway (I tried creating a whole new file to test just incase the latest upgrade to BI caused some issues)

On my desktop everything looks fine. Its only when I upload the file. I have tried filtering just to the current month so not too much load on the server (even though this is only a few thousand line table being queried)

Desktop.

BI Website - just get spinning circles for the tables and the graph for last 7 days does not work correctly.

File here - pbix

Thanks for looking at this - hopefully its just something simple I am doing wrong.

Rgds

ED