Forum Discussion

rajibmahmud's avatar
rajibmahmud
Helper 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
Helper III
9 years ago

Thanks Sir. :)

 

I did created a Calendar table. Let me go through and try, as I am new to this having some basic issues...

TomMartens's avatar
TomMartens
Super User
9 years ago

Time, DAX, both can become wild beasts especially when they meet ;-)

  • TomMartens's avatar
    TomMartens
    Super User
    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
    Helper III
    9 years ago

    TomMartens

     

    I am unable to make it work. I followed the same steps as shared in your file earlier as well. Whenever I put Date from my table, it works for sameperiodly, But when I try the same using Date from Calendar table which is linked with data/sales table, it doesnt work. Same goes for year. I added screenshot below.

     

    Working scenario, which is not usefulRelationshipCalendarSample data

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    Hey,

     

    can you upload your file and share the link, please.

     

     

  • rajibmahmud's avatar
    rajibmahmud
    Helper 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
    Super User
    9 years ago

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