MdxScript(Model) (27, 88) Calculation Error in measure

[Anonymous]

Hello Community

I've been struggling with one such measure.

Couldn't load the data for this visual

MdxScript(Model) (27, 88) Calculation Error in measure 'Sales Flash'[Total Invoice Amount LY (R)]: Function 'SAMEPERIODLASTYEAR' expects a contiguous select when the date column is not unique, has gaps or it contains time portion.

What does this mean in lamens terms? I'm using a tabular model (SSAS) behind this report and it was working prior to this error.

Thanks

Martin

[Anonymous · Accepted solution]

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! 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33377071-date-with-no-hierarchy-w...

[v-lili6-msft]

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

[amitchandak]

This happen when the dates have some break. To overcome this typically we create date table and use its date in such functions.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601