Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
danimelv
Frequent Visitor

SAMEPERIODLASTYEAR with filters Year,Month,Day

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.

danimelv_0-1651588216689.png

 

Any suggestion?

Attached PBIX 

 

5 REPLIES 5
amitchandak
Super User
Super User

,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

@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)

@danimelv , Try if this can help

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

It is not about showing the YTD from previous date:

 

danimelv_0-1651593107754.png

 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

@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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.