SAMEPERIODLASTYEAR WITH YTD

Hello all,

I am fairly new to Power BI, and I have been tasked with creating some Sales report, specifically a report with the YTD, MTD, QTD and well as the sameperiodlastyear for each. I have created a DateTable as shown below, which I added because I really belive it has to be datetable that is giving me problems. I have the below measures created, and I have made so so many iterations, I have confused myself. Either I get no data, when I add the sameperiodlastyear measure to the report, or it gives me the same data that I have for YTD, or it gives me nothing back, depending on how the measure was created. Can anyone see anything that sticks out to you immediately? Also, the only way it seems the ytd works, is if I create a filter on the date, but that seems counter-intuitive....any assistance would be greatly appreciated.

Measures:

same period last year sameperiodlastyear = CALCULATE(SUM(IHeads[Sales]),SAMEPERIODLASTYEAR('DateTable'[Date]))

smp lastyear = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DateTable[Date]))

YTD Sales = TOTALYTD([Total Sales],'IHeads'[DateKey]

let
Source = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & "'" & Text.Range(Number.ToText([Year]),2,2)),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertWeekStarting = Table.AddColumn(InsertWeekEnding, "WeekStart", each Date.StartOfWeek([Date]), type date)
in
InsertWeekStarting,
#"Invoked FunctionSource" = Source(#date(2016, 1, 1), #date(2016, 12, 31), null),
#"Changed Type" = Table.TransformColumnTypes(#"Invoked FunctionSource",{{"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DayInWeek", Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Date", "Date - Copy"),
#"Calculated Week of Year" = Table.TransformColumns(#"Duplicated Column",{{"Date - Copy", Date.WeekOfYear}}),
#"Renamed Columns" = Table.RenameColumns(#"Calculated Week of Year",{{"Date - Copy", "WeekofMonth"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}, {"DateInt", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Sorting Order", each [Year]*100 + [MonthOfYear]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each true),
#"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows",{{"Sorting Order", Int64.Type}}),
#"Inserted Start of Year" = Table.AddColumn(#"Changed Type2", "StartOfYear", each Date.StartOfYear([Date]), type date),
#"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "EndOfYear", each Date.EndOfYear([StartOfYear]), type date),
#"Changed Type3" = Table.TransformColumnTypes(#"Inserted End of Year",{{"Date", type datetime}})
in
#"Changed Type3"

Need Help

jblackshear
9 years ago
(I'm still pretty new at this myself but I encountered this situation)

I think your previous YTD is using the same context as the current YTD, or maybe you have a date filter on the whole matrix to report a specific year, so it isn't finding any data. Here is the thread with the problem I had and the solution.

SAMEPERIODLASTYEAR with a year filter

What I ended up doing was this (this is from my own solution, I haven't tried to fit it to yours)

RevenueLastYTD = CALCULATE([RevenueYTD], FILTER(ALL(Dates), Dates[CalendarYear]=MAX(Dates[CalendarYear])-1), SAMEPERIODLASTYEAR(Dates[Date]))

The key is using CALCULATE and FILTER. FILTER(ALL(Dates), ...) first opens up the dates context to use all dates again (because it may be currently set at a specific year, either because of a date filter on the whole matrix, or because of the row it is on). Then the next part of the filter statement sets a new filter for the previous year. I'm not entirely sure if that part is necessary since I'm calling SAMEPERIODLASTYEAR. I first did this several months ago and haven't worked with it much since then.

But take a look at using FILTER to open up the context in your previous year calculation.