Forum Discussion

ptmuldoon's avatar
ptmuldoon
Resolver I
2 years ago
Solved

SAMEPERIODLASTYEAR Contigous Date Issue

I am receiving a "only works with contigous data selections" when using the SAMEPERIODLASTYEAR function, and can't seem to figure out what is causing it.  Varous google searches seem to always suggest making sure all dates are there and a 1 to many relationship, which I do have.

 

I have a calendar table that is complete with no gaps or duplicates.  That calender table is actually being made via power query like such:

let
    //StartDate = #date(2019,1,1),
    StartDate = Date.FromText(Number.ToText(StartYear) & "0101"),
    EndDate = Date.AddMonths(StartDate,60),
    //EndDate = List.Max(PSData[Date]),
    NumberOfDays = Duration.Days(EndDate-StartDate)+1,

    DatesList = List.Dates(StartDate, NumberOfDays, #duration(1,0,0,0)),
    // Convert the list of dates to a table
    CalendarTable = Table.FromList(DatesList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(CalendarTable,{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Quarter", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted Day Name", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted End of Month", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Day of Year", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type)
in
    #"Inserted Week of Year"

 

My Calendar table has a 1 to many relationship to a data table.

 

 

And my DAX measures trying to compute the YTD Variance between this year and last year.

 

YTD Amount:=TOTALYTD ( 
      [Amount],
       'Calendar'[Date],
       "12/31"
)

YTD Amt Var to LY:=[YTD Amount] - 
	CALCULATE (
		[YTD Amount], SAMEPERIODLASTYEAR('Calendar'[Date]
		)
	)

 

And when I try to use the YTD Amt measure with a filtered date of January to November (excluding Dec), I get the contiguous date error message?

2 Replies