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

Anonymous Thanks alot.

 

Another noob query I have, If my table have Year and Month in seperate column. Month in Jan, Feb, Mar - This format (First 3 letter).

 

Can I use time intelligence formula on this scenario?

 

Sample data

 

YearMonthCurrency
2017Jan100
2017Feb200
2017Mar300
2016Jan100
2016Feb200
2016Mar300
Anonymous's avatar
Anonymous
Not applicable
9 years ago

You basically MUST have a calendar table.
http://tinylizard.com/power-bi-date-table/

 

I would create a calculated column that appends Year/Mo/1 to create a real date, hook that up to a date table.