Forum Discussion
MdxScript(Model) (27, 88) Calculation Error in measure
The solution to this was that I had a date filter going in both directions from my date table to my date fact table. I changed this to one way.
The reason I had to have a date fact table is because there were multiple companies in my date table with their own fiscal year calendars.
Thanks for your replies, they help me ascertain what the problem was and reconfirmed that I was doing the right thing in regards to the time intelligence piece. Also, note, I had encountered another issue when dealing with tabular modelling when dealing with the date hierarchy's...SSAS passed them through to Power BI as text!
hi Anonymous
All of the time intelligence functions require a date table (more like a date column) as a parameter. That date column has to include every date (say, incluing weekends and holidays) with no "gaps". That is what is meant by continguous (which, sounds similiar to "continuous").
Some weird things can happen though, if you are trying to use these time intelligence functions like SAMEPERIODLASTYEAR... if you use a slicer to remove rows from the middle. Same period last year wants to look at just exactly 1 "period" -- that can be a week, day, month, 4.6 weeks... but they need to be 1 continuous range not like "the 1st and 4th week of July" (which would have a gap... in the 2nd and 3rd weeks, and is not allowed).
And since you are using a tabular model (SSAS) behind this report, so this report is Live connection, so you need to create a separate date table, then relate your current table, to the date table in the SSAS.
Then create the measure use SAMEPERIODLASTYEAR('Date'[Date]) instead of SAMEPERIODLASTYEAR('fact data'[Date])
Regards,
Lin