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

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