Filter datatable from current month and current user

[cf_amart]

I want to filter the DataTable from Sharepoint to get only the data for the current month and the current user. 

Here is what I have

Filter('Table Name', Date = Date(Year(Today()), Month(Today()), 1) && User().FullName ='Created By'.DisplayName)

 

If I do one condition at a time, the table populates. But it does not work with 2 conditions. 

[gabibalaban]

Hi @cf_amart ,

There doesn't seem to be anything wrong with your formula, except for delegation issues. Are you sure that there are items in the list that simultaneously meet those conditions?

Regarding the delegation issues, try to define context variable, like this:

UpdateContext({myDate:Date(Year(Today()),Month(Today(),1)});

UpdateContext({userName:User().FullName})

and use it variable inside your formula:

Filter(yourList,Date=myDate && userName='Created By'.DisplayName)

Hope it helps !

[cf_amart]

The delegation error is saying "the formula might not work correctly on large data sets". Yes, I myself have entered data for this current month, so it should be showing some rows. 

[FabianAckeret]

Hi @cf_amart 

Your condition is checking whether you have some data entered on the FIRST of the current month. So that would be the 1st of January. Can you check if this is true?

Other than that, I would also recommend you to not check against a display name. A better solution would be to filter for user Principal Names. Or Claims, if you're working with SharePoint. However, that is not the reason why no data is being shown.

I assume it might be a case sensitive issue. Try the following:

Filter(
   'Table Name', 
   Date = Date(Year(Today()), Month(Today()), 1),
   Lower(User().FullName) = Lower('Created By'.DisplayName)
)

Kind regards,

Fabian

[cf_amart]

Filter('Inventory Entries Hobbs', Date = Date(Year(Today()), Month(Today()), 1), Upper(Office365Users.MyProfile().DisplayName) = Upper('Created By'.DisplayName))

Correct, all entries are made on the 1st of each month (I have that fixed in my DatePicker already). 

I tried the upper and lower for case sensitive, and the datatable is still empty.  I also tried using the Office365Users function instead. 

[cf_amart]

If I hardcode in a name (mine or other users), the table works perfectly with the date filter. 

Filter('Inventory Entries Hobbs', Date = Date(Year(Today()), Month(Today()), 1), "My Name" = 'Created By'.DisplayName)

[cf_amart · Accepted solution]

Here is what I did to make it work.

Created a label with Items = User().FullName.

Then in the Filter function of the data table, after the date = Date(...), Label.Text = 'Created By'.DisplayName

[FabianAckeret]

@cf_amart 

Is SharePoint your data source?

Either way, I would always recommend to use the value from the data source BEFORE the value you're trying to match against.

E.g.:

Filter(
   'Table Name', 
   Date = Date(Year(Today()), Month(Today()), 1),
   Lower('Created By'.DisplayName) = Lower(User().FullName)
)

and to make it bulletproof and definitely sort out ambiguity, you could do this:

Filter(
   'Table Name' As tempTable, 
   tempTable.Date = Date(Year(Today()), Month(Today()), 1),
   Lower(tempTable.'Created By'.DisplayName) = Lower(User().FullName)
)

I hope this helps.