Time intelligence Last 3 year

[rajibmahmud]

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

[Anonymous]

Total Value:=SUM(Table2[Value])

YTD:=calculate([Total Value], DATESYTD(Dates[Date]))

1YP:=CALCULATE([Total Value], DATEADD(DATESYTD(Dates[Date]), -1, YEAR))

2YP:=CALCULATE([Total Value], DATEADD(DATESYTD(Dates[Date]), -2, YEAR))

[rajibmahmud]

@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

[TomMartens]

Hey,

no it's not possible to use time intelligence funtions, these functions require a column of the datatype date/datetime.

For this reason, a date / datetime column is created even if the granularity of the values is just month, as it seems to be in your scenario.

In this case, the 1st day of the month is used to connect the currency table with a separate date table.

Basically it's possible to rewrite the time intelligence functions in "normal" DAX, but this is a more complex task, but also if you try this, it'almost mandatory to use a data/datetime column and a separate date/datetime table (calendar table).

Even if you use standard (international) abbreviations for the months, it is not recognized as a month, Power BI is not capable to order the values of the month column.

I highly recommend to have a closer look at the design and usage of calendar tables, for time intelligence calculations this is always a good starting point:

http://www.daxpatterns.com/time-patterns/

Hope this helps

[rajibmahmud]

@TomMartens & @Anonymous , Thanks alot.

On that scenario, I guess I have to create a Custom column during data load to convert Month & Year to Date for example 2017 Jan would be 01/01/2017.

Any idea how I would be able to do that?

Probably there would be 2 steps.

Step 1: Convert Jan to January and Step 2 would be converting this combination to Date.

Or is there any other simpler way to do it using Relationship table and other stuff to achive this?

I was trying resolve this issue by adding Custom column, however I am facing issue as the month's are in 3 letter format. Which I might be resolved by adding another column for converting month. But this will complicate my DB and increase the size. Is there any other way around? I have a mapping table in the DB where Jan = January and 1.

Is there any option to add Lookup or similar formula in Convert to date formula? I will use Date(Year columne, " Not sure yet" , 1)

[rajibmahmud]

I counter the issue of Date using 

FDate = Date('Sales'[Year],
LOOKUPVALUE( Calendardb[Month], Calendardb[MonthCode], 'Sales'[Month]) ,
1)

However, my time intelligence formula is for Prior year value is not working

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

And it resulting below. I dont see value beside year for new measure... And I am not sure about the total value.

Year	Value	PY
2014	31,884
2015	32,836
2016	34,095
2017	18,638
Total	117,454	82,755

[TomMartens]

Hey,

this is an answer for your question how to create a datetime column in your currency table. Here is a little pbix file.

Basically I created an index table, this table contains a column with 3letter monthnames and an index column 1 for Jan ...

I merged this table to currency table to retrieve the index for the month, then I created a datetimecolumn using the already existing year column and the monthindex column, additionally i passed constant values for day and hour, min and sec.

Be aware that, you have to align the time part across all your date / datetime columns. Sometimes it is sufficient just to create just a date column, in these scenarios you can omit the parameter for the time and you can use this formula instead:

Date.From(#date(year, monthno, dayno)).

Then I created a very basic calendar table in the model, but you also can create the calendar table in query part (before the loading to the model).

I created a relationship between both table, and have also hidden the helper table and some columns from the report view,

I hope this gets you started.

Have a look at the link Scott provided and also at the daxpattern.com site.

Until you don't have calendartable the timeintelligence functions won't work

[rajibmahmud]

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]

Time, DAX, both can become wild beasts especially when they meet 😉

[rajibmahmud]

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

Hey,

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

[rajibmahmud]

@TomMartens

Uploaded to below link

https://www.dropbox.com/s/vwn61cf2uye6xuv/UAT%20Environment.pbix?dl=0

[TomMartens · Accepted solution]

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]

Thanks a lot 🙂 🙂

Same to you 🙂

My weekend is over... Fri-Sat day is weekend here.. So office day starts from tomorrow...

[TomMartens]

Then I guess you are well prepared for the upcoming week 🙂

[Anonymous]

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.