SAMEPERIODLASTYEAR WITH YTD

[umpoohg]

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"

[v-yuezhe-msft]

Hi @umpoohg,

Do you use the following formula to create [Total Sales]?

[Total Sales] = SUM(IHeads[Sales])

If that is the case, use the following formula to create YTD sales. After that, use date columns from DateTable and “YTD Sales”, “same period last year sameperiodlastyear” measures from IHeads table to create visual , then check if the measures return expected result.

YTD Sales = TOTALYTD(SUM(IHeads[Sales]), DateTable[Date])

There is an example for your reference.

Thanks,
Lydia Zhang

[umpoohg]

Hi Lydia,

The problem that I am really having is I can get year to date, however I am unable to get the sameperiodlastyear to work.  

Prev Prd Sales = CALCULATE(SUM('IHeads'[Sales]), SAMEPERIODLASTYEAR(DateTable[Date]))

Prev Prd Sales = CALCULATE(SUM('IHeads'[Sales]), SAMEPERIODLASTYEAR(DateTable[Date]))

Both methods still bring in the same exact values.  The amount shown is the total sales for 2016, so that is not exactly right.  I am just looking for YTD or (01/01/2017 - 02/27/2017) and then beside that the sameperiodlastyear or (01/01/2016 - 02/27/2016), as shown above.

Any assistance would be appreciated.

[v-yuezhe-msft]

Hi @umpoohg,

I understand your scenario. Have you added year and month columns to your visual? You can review the screenshot in my first reply, sameperiodlastyear returns correct values.

Check the following similar blogs to get details about which scenario sameperiodlastyear function applies to .

https://bipassion.wordpress.com/2012/08/26/dax-sameperiodlastyear-and-datesytd/
http://www.wiseowl.co.uk/blog/s2477/same-period-previous-year.htm

Thanks,
Lydia Zhang

[umpoohg]

Hi @v-yuezhe-msft

I have tried so many variations of this, it has to be related to my date table, in my opinion.   I just have not been able to tweak the table.  Do you mean add the month and year to the values or the grid, when I do that, it explodes the data, but it does show that the correct months are not being displayed.  I would expect to only see data from Jan and Feb of 2016 and 2017..

[v-yuezhe-msft]

Hi @umpoohg,

Create a Matrix visual as shown in the following screenshot, then filter the years using a slicer based on your needs. But in this sceanrio, we are not able to filter month as SAMEPERIODLASTYEAR only works with contiguous date selections.



Thanks,
Lydia Zhang

[umpoohg]

Hi @v-yuezhe-msft,

Unfortunately, the requirement needs to be in a grid, per the user.  So the matrix will not work, in the way that you have it formatted.  I am starting to think that it cannot be done, in the way I want it.  To use 'Date Intelligence' tables, I would think I would not need a slicer.  If I am requesting Year To Date, the system should know I am talking about this year, until today.  I would also think the system would know when i am asking for the sameperiodlastyear, it is looking for 01/01 - 03/01 of 2016, which represent the period we are in now, but last year.

Anyway, thank you for looking.

[umpoohg]

Hi @v-yuezhe-msft,

Unfortunately, the requirement needs to be in a grid, per the user.  So the matrix will not work, in the way that you have it formatted.  I am starting to think that it cannot be done, in the way I want it.  To use 'Date Intelligence' tables, I would think I would not need a slicer.  If I am requesting Year To Date, the system should know I am talking about this year, until today.  I would also think the system would know when i am asking for the sameperiodlastyear, it is looking for 01/01 - 03/01 of 2016, which represent the period we are in now, but last year.

Anyway, thank you for looking.

[jblackshear · Accepted solution]

(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.

[umpoohg]

In your dates table what is calendar year, is that just the Year i.e. 2017?  I feel like it has to be the date table, I have seen your post earlier, but that is not really working either.  I am not getting a message that I do not have all the dates in my date table.  However, I am pretty sure I have more dates in my date table than I do in the header(facts)table. 

Thank you so much

[umpoohg]

I tried this:  ThisPeriodLY = CALCULATE(IHeads[Total Sales],FILTER(ALL(DateTable[Date]),DateTable[Date]=MAX(DateTable[Year])-1),SAMEPERIODLASTYEAR(DateTable[Date]))

But I get no values

I wish I could just see an example where this has been done before, so I can at least know it is possible.

[jblackshear]

Here are the formulas I used for the measures.

Total Revenue = SUM(SalesDataAggregated[Revenue])

Total Revenue This Period LY = CALCULATE(SalesDataAggregated[Total Revenue], FILTER(ALL(Dates), Dates[CalendarYear] = MAX(Dates[CalendarYear])-1), DATESMTD(SAMEPERIODLASTYEAR(LASTDATE(SalesDataAggregated[Date]))))

I cannot fully explain the use of LASTDATE in the formula. I did this several months ago and have had to work on other projects since then. At the time, I was doing a lot of reading and research, and I believe LASTDATE is there so I don't compare an incomplete current month this year to a complete month last year.

 Edit to add:

A big question I have looking at your sample output is - what is the period? I'll go back and reread the thread to see if you explain it there.

[jblackshear]

I'm beginning to understand what you're trying to do - almost.

There is a DAX function Now(). You can use it with Year() to always have the current year for your calculation. Something like

Year(Now())

https://social.technet.microsoft.com/wiki/contents/articles/679.power-bi-dax-date-and-time-functions...

You may also want to understand DAX row context and filter context. It's pretty complicated, but important.

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

[jblackshear]

I gave it a try with my data. I don't have time to dig in and see if it's truly correct, but something here may get you on the right track.

My new measures created for your requirements:

RevenueCurrentYTD = CALCULATE(SalesDataAggregated[Total Revenue], Dates[CalendarYear] = Year(Now()))

RevenueCurrentPeriodLY = CALCULATE(SalesDataAggregated[Total Revenue], FILTER(ALL(Dates), Dates[CalendarYear] = YEAR(NOW())-1), DATESMTD(SAMEPERIODLASTYEAR(LASTDATE(SalesDataAggregated[Date]))))

I was able to create a quick table with my product categories, Revenue YTD (for the current date), and Revenue for the current period last year. Many of the current period last year values were blank because we just didn't have sales for those categories in that month last year. But there were values for the other product categories and they seemed reasonable.

Try it, use what you can from it, and good luck!

[umpoohg]

I am trying to your suggestions now, thank you so much! @jblackshear

[umpoohg]

Hi @v-yuezhe-msft

I have tried so many variations of this, it has to be related to my date table, in my opinion.   I just have not been able to tweak the table.  Do you mean add the month and year to the values or the grid, when I do that, it explodes the data, but it does show that the correct months are not being displayed.  I would expect to only see data from Jan and Feb of 2016 and 2017..

[Datatouille]

Hi @umpoohg

Try these measures:

Sales YTD = Calculate ( SUM('IHeads'[Sales] ) , DatesYtd( DateTable[Date] ) )

Sales YTD LY = Calculate ( [Sales YTD] , SamePeriodLastYear( DateTable[Date] ) )

You can also leverage DAX variables and embed them to compute Sales YTD LY:

Sales YTD LY 2 = VAR YTDTab = DatesYtd(DateTable[Date]) VAR YTDLYTab = SamePeriodLastYear( YTDTab ) RETURN

Calculate (  SUM('IHeads'[Sales]) , YTDLYTab )

[umpoohg]

Hi @Datatouille,

Thank you for your response, however below are the results...so strange?!?!?!?!

Sales YTD = Calculate ( SUM('IHeads'[Sales] ) , DatesYtd( DateTable[Date] ) )

Sales YTD LY = Calculate ( [Sales YTD] , SamePeriodLastYear( DateTable[Date] ) )

Seems it is basically doing what the measures I have created are doing...