Forum Discussion

rajibmahmud's avatar
rajibmahmud
Icon for Helper III rankHelper III
9 years ago
Solved

Time intelligence Last 3 year

Using DAX time intelligence function we could compar Current year data vs last year data or Last year same period data.   Is there any option to do it for last 3-4 year data?   What I am looking ...
Need Help
  • TomMartens's avatar
    TomMartens
    9 years ago

    Hey,

     

    there was just a little issue ;-) Here you find your slightly modified pbix file

     

    Your Formula

    PY = CALCULATE(sum('Sales'[Value]), SAMEPERIODLASTYEAR('Sales'[FDate]))

    Working Formula

    PY Working = 
CALCULATE(
	sum('Sales'[Value]), 
	SAMEPERIODLASTYEAR('Calendardb'[Date])
	)

    The difference:

    It always (ok - in most cases) to filter the fact table (Sales) using dimension tables (CalendarDB). For this reason the function SAMEPERIODLASTYEAR has to reference the dimensiontable. SAMEPERIODLASTYEAR() expands the current filter of the CalendarDB table, and these rows are propagated to sales table using the relationship (CalendarDB --> Sales)

     

    Voila :-)

     

    Have a nice weekend

rajibmahmud's avatar
rajibmahmud
Icon for Helper III rankHelper III
9 years ago
Thanks a lot :) :)

Same to you :)

My weekend is over... Fri-Sat day is weekend here.. So office day starts from tomorrow...
TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
9 years ago

Then I guess you are well prepared for the upcoming week :-)