Forum Discussion
Time intelligence Last 3 year
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
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...
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
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.
Working scenario, which is not usefulRelationshipCalendarSample data
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- Thanks a lot :) :)
Same to you :)
My weekend is over... Fri-Sat day is weekend here.. So office day starts from tomorrow... Then I guess you are well prepared for the upcoming week :-)