Forum Discussion
Responsive ResidentRelative Date & Date Offset formula stopped working
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.
4 Replies
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 Anonymous 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.
Thanks for looking at this - hopefully its just something simple I am doing wrong.
Rgds
ED
- elliotdixon
Hi Anonymous 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
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
