LastPeriods & Ancestor

[Stephaneky]

I have inherited a report/ dashboard that allows the end user to pick an "as of date" to change how a bar chart is displayed. The chart shows sales for the last 13 months and the feature described is designed to pick any date in the past (i.e. 13 months from December 2017). This is what the formula used to perform this looks like :

for 13 months:



lastPeriods (13,ancestor(item(filter ([Sales].[Time].[Time_H].[Day], caption([Sales].[Time].[Time_H].[Day])
= #sq(timestampMask ( toUTC( prompt ( 'PDate' , 'Date' ,  timestampMask ( _add_days ($current_timestamp,-1) , 'yyyy-mm-dd' )  ) +' 00:00:00.000-00:00'), 'yyyy-mm-dd' ))  #),0),1)



The problem I am faced with is that this displays complete months even if the date selected is in the middle of the month. To use the December 2017 example, whether I pick December 15 2017 or December 31 2017 the function will display the same size bar for the month of December. I need to increase the degree of granularity of this formula to take into account the specific day selected and therefore only displaying data older than the date selected.

Any ideas?