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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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'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.

    • 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)