I have a power pivot to with daily sales tables (one table with sales this year and one with sales last year) and date reference table. The user inputs the date and populates on the data model. Then, I have a lookup to show gross sales if the date is less or equal to the date on date referenc table.
See screenshots for more info.
I have this Data Reference table on my PowerPivot where I have a calculation to show me the same date (user entered and populate Date - 1st column below) last year which is column below Date LY.
Then, I have a table for sales last year where I use that Date LY to show gross sales YTD. See below formula where it should find the Docdate and compare if less or equal then show the gross sales, else 0. It shows only the lines for the month but it will not include anything prior (like January thru May).
Same thing happens with comparing date to sales this year. Not sure if something missing on my RELATED (Date reference) formula.