DATESINPERIOD seems to be broken in the service

[ericleigh007]

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:

and the desktop

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.

[v-huizhn-msft]

Hi @ericleigh007,

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



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))

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

Count of DATES_TEST = COUNTROWS(DATES_TEST)

I publish it to service, it still works fine.



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

Thanks,
Angelia

[ericleigh007]

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]

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]

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]

Hi @ericleigh007,

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

Thanks,
Angelia

[ericleigh007]

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]

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]

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

-thanks

-e

[TaylorClark]

@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. 

[TaylorClark]

@ericleigh007 DATESINPERIOD (and all other "time intelligence" functions) require the date column parameter contain a contiguous set of dates, with no blank rows.

YourTable

Date

2/1/2017

2/4/2017

For for instance, this 2 row table doesn't meet those requirements, so DATESINPERIOD will return an empty table, or an error depending on where you use the function.

As part of Date support in Power BI Desktop we automatically create a hidden date table (using the CALENDAR function) that meets those requirements by filling in the missing values for each of your date columns. You can access that column by using DAX variation syntax:

TestTable = DATESINPERIOD(YourTable[Date].[Date], NOW(), -100, YEAR)

That expression will return the following:

TestTable

Date

2/1/2017

2/2/2017

2/3/2017

2/4/2017

Notice that this table contains dates that weren't in YourTable[Date]. If you want only the intersection of these, you can use either of these:

FILTER(YourTable[Date], [Date] in DATESINPERIOD(YourTable[Date].[Date], now(), -100, 

INTERSECT(VALUES(OpenCharging[Subperiod_Ending_Date]), DATESINPERIOD(OpenCharging[Subperiod_Ending_Date].[Date], now(), -100, YEAR))

Hope this helps!