Forum Discussion

AndyTrezise's avatar
AndyTrezise
Icon for Advocate IV rankAdvocate IV
8 years ago

filter table to current month + 2

Is it possible to filter a table to only include rows for current month + 2...i.e. I don't want current month, or next month, but the month after that

 

My data connector is ODATA (I believe it to be V3)

 

Using the query editor...

 

= Table.SelectRows(#"Sorted Rows", each Date.IsInNextMonth([Date]) )        Gives me next month

= Table.SelectRows(#"Sorted Rows", each Date.IsInNextNMonths([Date], 2) )      Gives me next month and the month after that

 

I want to skip next month and just select the following months data

 

I thought this might work:

 

= Table.SelectRows(#"Sorted Rows", each Date.IsInNextMonth( Date.AddMonths([TimeByDay],1) ) )

 

But I just get an error...

 

DataSource.Error: OData: Request failed: The remote server returned an error: (500) Internal Server Error. (Unable to create platform context. Please ensure that you have Log On Permissions on the server)
Details:
    DataSourceKind=OData
    DataSourcePath=https://mysite.sharepoint.com/sites/UKPWA/_api/Projectdata/AssignmentTimephasedDataSet
    SPRequestGuid=481b329e-d0d5-4000-79cf-c576987e49c0
    Url=https://mysite.sharepoint.com/sites/UKPWA/_api/Projectdata/AssignmentTimephasedDataSet?$filter=TimeByDay ge datetime'2016-01-01T00:00:00' and AssignmentWork gt 0&$select=AssignmentId,AssignmentWork,ProjectId,ResourceId,TaskId,TaskName,TimeByDay&$skiptoken=guid'4ad55920-b8a9-e711-96a7-4851b7dcbd4b',guid'b8d85920-b8a9-e711-96a7-4851b7dcbd4b',datetime'2017-06-14T00:00:00'

2 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion

    This would be my suggestion:

     

    = Table.SelectRows(#"Sorted Rows", each not Date.IsInNextMonth([Date]) and Date.IsInNextNMonths([Date],2))