Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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))
@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 |
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
@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)
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 |
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
Thanks Sir. 🙂
I did created a Calendar table. Let me go through and try, as I am new to this having some basic issues...
Time, DAX, both can become wild beasts especially when they meet 😉
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.
Hey,
can you upload your file and share the link, please.
Uploaded to below link
https://www.dropbox.com/s/vwn61cf2uye6xuv/UAT%20Environment.pbix?dl=0
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
Then I guess you are well prepared for the upcoming week 🙂
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.