I assume that the [Reporting day] holds numeric value and you want to validate it with the day part of date field.
the datetrunc() function returns data in date format i.e. "dd/mm/yyyy" hence it may be giving you wrong result can you please try the same using
DATEPART('day',[Date]) = [Reporting Day]
Sankarmagesh Rajan Yes, I have seen this solution. But just as I said in my original message, there should be no user interaction on this step. This solution is only half good because it requires manual switch between last period and custom period.
Anupam Mehta No, [Reporting Day] is a date parameter and it works just fine with the DATETRUNC function. It allows to pick custom day, but not relative in the same time.
Rahul Singh thanks, that's the closest to solving the problem, but still not enough. By last day I actually meant previous, as the data need to be complete for the whole day, so anyone using the dashboard should actually see the day before current day. This option will always pick the last value in the database, even If I exclude current day with some calcs. The result will be an empty workbook then
Now place this on the filters shelf , select true and add it to context.
Now bring the date field into filter and select the option show values only in context.
As you change the value for the parameter you can see the range of order date adjusting accordingly.
To see the day before yesterday data select the value of parameter as 1.
Hope this helps.
Guys, thanks for all your help. I managed to solve it by myself finally, using first method Rahul Singh posted but in the same time adding a boolen calculated filed exluding today into datasource filters!
Now the option to "Filter to the latest value when workbook is opened" works with a previous day as exclusion is made at datasource level. Simple as that
All the best!