Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
elliotdixon
Responsive Resident
Responsive Resident

Relative 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

2016-12-12 08_14_46-.png

 

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 4
v-shex-msft
Community Support
Community Support

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?

 

Capture.PNGCapture2.PNG

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

2016-12-14 16_29_54-Test All Customers Live - Copy - Power BI.png

2016-12-14 16_30_12-Test All Customers Live - Copy - Power BI.png

2016-12-14 16_32_59-Test All Customers Live - Copy - Power BI.png

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

2016-12-14 12_12_33-Test Relative Date - Power BI Desktop.png

 

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

2016-12-14 12_02_41-Test Relative Date - Power BI.png

 

File here - pbix

 

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

 

Rgds

ED

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.