cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ericleigh007 Regular Visitor
Regular Visitor

DATESINPERIOD seems to be broken in the service

The following formula works on the dekstop, but seems to fail in the service:

 

 

DATES_TEST = FILTER( OpenCharging, OpenCharging[Subperiod_Ending_Date] IN DATESINPERIOD( OpenCharging[Subperiod_Ending_Date], now(), -4, MONTH))

 

Substituting this instead gets things working again.

 

 

RecentCharging = FILTER ( OpenCharging, OpenCharging[Entry_Age] < 16 )

 

I added some measures to my dashboard to isolate the problem...  Hypothesis: The service is not properly filtering the DATES_TEST table, but the filter DOES work on my desktop.

 

Here's the service:

service datesinperiod.png

and the desktop

 

desktop datesinperiod.png

Notice that the DATES_ROW_COUNT is correct on the desktop, but that same table is blank on the service.

 

Hopefully others are seeing this problem and can confirm.

 

 

 

10 REPLIES 10
v-huizhn-msft Super Contributor
Super Contributor

Re: DATESINPERIOD seems to be broken in the service

Hi @ericleigh007,

I create the sample table. I try to reproduce your scenario and get expected result.

1.PNG

I click "New Table" under Modeling on Desktop Home page using your formula.

DATES_TEST = FILTER( OpenCharging, OpenCharging[Subperiod_Ending_Date] IN DATESINPERIOD( OpenCharging[Subperiod_Ending_Date], now(), -4, MONTH))

2.PNG

I create a measure to get the count rows of the table.

Count of DATES_TEST = COUNTROWS(DATES_TEST)

3.png

I publish it to service, it still works fine.

4.PNG

Did you get any error message? Do you mind share your .pbix file for further analysis?

Thanks,
Angelia

ericleigh007 Regular Visitor
Regular Visitor

Re: DATESINPERIOD seems to be broken in the service

Sounds like this one is not as easy to duplicate as I thought/hoped.

 

Let me do some simplication on my end and get back.

 

-e

ericleigh007 Regular Visitor
Regular Visitor

Re: DATESINPERIOD seems to be broken in the service

By all accounts, the service update seems to have gone fine.  The only indication i get that things are broken is that the data is missing.

 

Ddi you happen to test with tens of thousands of rows of data?   I suspect now that the problem is the amount of data collected by the DATESINPERIOD collection.   As I understand it, that will make a unique list of dates from the column.  I'm looking at cutting down the number of rows sent to DATESINPERIOD at this time.

ericleigh007 Regular Visitor
Regular Visitor

Re: DATESINPERIOD seems to be broken in the service

I did some pre-filtering, like this

 

DATES_TEST = FILTER( FILTER( all(OpenCharging), OpenCharging[Entry_Age] < 3 ), OpenCharging[Subperiod_Ending_Date] IN DATESINPERIOD( OpenCharging[Subperiod_Ending_Date], now(), -4, MONTH))

 

in my case, an entry age of 3 returns 819 rows, and the service fails at that point.  If I make the entry age check 2, the "prefilter" returns 407 rows, and then the DATESINPERIOD check returns data.

 

Any value of the entry age, and even removing the prefilter works fine in desktop.

 

Can you expand your test case accordingly and re-test?

 

If not, i'll get some time later this afternoon to get back to this.

 

-thanks

-e

v-huizhn-msft Super Contributor
Super Contributor

Re: DATESINPERIOD seems to be broken in the service

Hi @ericleigh007,

It seems the DATESINPERIOD can return results, but the result is wrong?

Thanks,
Angelia

ericleigh007 Regular Visitor
Regular Visitor

Re: DATESINPERIOD seems to be broken in the service

As I mentioend, it returns results in the Desktop for any number of rows and distinct values of date that I've found.  In the service it works well with a lower number of rows ( about 400 ), but fails somewhere between there and 819 rows.   

 

When it fails, the service returns no rows, whereas the desktop works fine.   When it works, the desktop and the servcie return the same number of rows.

v-huizhn-msft Super Contributor
Super Contributor

Re: DATESINPERIOD seems to be broken in the service

Hi @ericleigh007,

Please save and click your file. Then check your file size, the limit of file loaded to Power BI service is 1 GB. If it lead to the result?

Thanks,
Angelia

ericleigh007 Regular Visitor
Regular Visitor

Re: DATESINPERIOD seems to be broken in the service

The file is only 10.5MB, so that isn't it.

 

-thanks

-e

Power BI Team TaylorClark
Power BI Team

Re: DATESINPERIOD seems to be broken in the service

@ericleigh007, Could you PM me so that I can get your file to investigate this bug? Sorry to see you've had such trouble getting to the bottom of this. My hunch is that it may have something to do with the NOW() in the service using NOW for whereever your data is stored vs Desktop where it's using local time on your machine. 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 27 members 1,008 guests
Please welcome our newest community members: