SAMEPERIODLASTYEAR with filters Year,Month,Day

[danimelv]

Hi everyone,

I've been strugling with a requirement. I've read a lot of posts and previous answers to this issue but none of them works as spected, as I have a filter at day level.

I'm attaching a PBIX, where  I need to get the YTD of the Same Period of Previous Year. It works when I use the TODAY() function, but I want to show a filter with Year, Month and Day, so that, if the users select e.g, Year:2018; Month:Feb; Day:3 the SamePeriod of previous year must calculate unti Year:Year-1; Month:Feb; Day:3.

The Tab "Page1", has a table with the expected results, using Today function: "Sales PY SamePeriod OK"

The tab "Page2" has some attempts to achieve the requirement with no  success: Please note, as the last date selected on the slicers is 02/02/2018, the PYSamePeriod should only show data until February of every year.

It is showing data until May as it is the last month with sales.

Any suggestion?

Attached PBIX 

[amitchandak]

,try like

Sales PYTD = 
CALCULATE( [Total Sales], datesytd('Calendar'[Date]) , filter('Calendar', format('Calendar'[Date],"MMDD") <=format(today(),"MMDD")  ))


Sales PY SamePeriod OK = 
CALCULATE( [Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date]) , format('Calendar'[Date],"MMDD") <=format(today(),"MMDD")  )

@danimelv

[danimelv]

@amitchandak 

Thanks for answering. Maybe I didn't explain well.

The problem is if I apply a filter with the slicers from the "page2". If you select e.g: Year: 2018, Month:Feb, Day:3; the result is not correct as it should display values for previous years until 2018/02/03.

In the formula you are providing, you are using the function TODAY() (I'm not interested on getting YTD until Today, but, as mentioned, until 2018/02/03-or whatever the user select on the slicer)

[amitchandak]

@danimelv , Try if this can help

CALCULATE( [Total Sales], dateadd('Calendar'[Date],-1,YEAR) )

[danimelv]

It is not about showing the YTD from previous date:

 Have you tried the formulas you provided? From the image, for 2017 year, I don't want YTD from March, April,May, As in the slicer are selected dates until 2nd of February

[amitchandak]

@danimelv , got it now. The selected date only works for the current year to get last year's number, as in last year those dates are not getting filtered it is not working. You have selected complete last year