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

 

  • rajibmahmud's avatar
    rajibmahmud
    Helper III
    9 years ago

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

      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.

       

      YearValuePY
      2014                       31,884 
      2015                       32,836 
      2016                       34,095 
      2017                       18,638 
      Total                     117,454                    82,755
      • TomMartens's avatar
        TomMartens
        Super User
        9 years ago

        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