missing fiscal quarter

[Anonymous]

hi all...

I have this missing quarter in my table thus having the following error msg. But i am not sure how i should correct it. the same set of data was working fine few months ago until 2020 data was added.

Error Message:
Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion.

https://gofile.io/d/4PHQ3Z 

Any advice is grateful. Thank you

[jairoaol]

so that time intelligence functions don't present problems you have to use a date chart

SAMEPERIODLASTYEAR( TABLA_FECHAS(DATE) )

This date table must have all the dates in your fact table. from the oldest date to the present and even future dates.

here's an example of how to create it: https://myprojectserver.wordpress.com/2019/08/12/tabla-de-fechas-en-power-bi-con-festivos-colombia-p...

[amitchandak]

@Anonymous , I checked You do not have dates after 08-Oct-2020 in the calendar. And you have data after that. I chekced Pbix. Please have additional date in calendar

[Anonymous]

@amitchandak can I know how do i additional date in existing calendar table
Thank you for helping

[amitchandak]

@Anonymous , You have stopped your calendar in Power Query till today. Make sure it goes till last date of the fact.

[Anonymous]

@amitchandak could you explain a little. i didnt know how i stopped the calendar in Power Query till today.

How do i get it to goes till last date of my data which is dec 2020?

thank you

[amitchandak · Accepted solution]

@Anonymous , This is one

#"Invoked FunctionSource" = Source(#date(#"Start Year", 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - #date(#"Start Year",1,1)), #duration(1, 0, 1, 0)),

That should go max of sales or like Date.FromText("2020-12-31")

[ToddChitt]

Tre this:https://docs.microsoft.com/en-us/dax/calendar-function-dax

My Dates Table = CALENDAR(<start_date>, <end_date>)

Adjust the End Date above to December 31 2020

[ToddChitt]

Date functions like SAMEPERIODLASTYEAR work best when there is a CONTIGUOUS list of dates. That can't happen if you reference some Date type column in your FACT table. You should create a Date dimension table and join it to the fact table, then when you use the function, it looks like this: SAMEPERIODLASTYEAR('Dates Table'[Date Field])

Hope that helps.