cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rajibmahmud Member
Member

Re: Time intelligence Last 3 year

@TomMartens

 

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.

 

1.JPGWorking scenario, which is not useful2.JPG3.JPGRelationship4.JPGCalendarSales Table.JPGSample data

Super User
Super User

Re: Time intelligence Last 3 year

Hey,

 

can you upload your file and share the link, please.

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
rajibmahmud Member
Member

Re: Time intelligence Last 3 year

Highlighted
Super User
Super User

Re: 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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
rajibmahmud Member
Member

Re: Time intelligence Last 3 year

Thanks a lot Smiley Happy Smiley Happy

Same to you Smiley Happy

My weekend is over... Fri-Sat day is weekend here.. So office day starts from tomorrow...
Super User
Super User

Re: Time intelligence Last 3 year

Then I guess you are well prepared for the upcoming week :-)

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!