Forum Discussion
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 for is based on Current year selection BI will provide me not only LY but also -2 and -3 year values.
Result could be like below table.
| CY | Sameperiod LY | 2 Years back | 3 Years back | |
| Margin | 100 | 90 | 80 | 50 |
Sample Data
| Year | Currency |
| 2017 | 100 |
| 2016 | 90 |
| 2015 | 80 |
| 2014 | 50 |
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
15 Replies
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
Year Month Currency 2017 Jan 100 2017 Feb 200 2017 Mar 300 2016 Jan 100 2016 Feb 200 2016 Mar 300 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.
