Help with date measures - Yesterday, Year to do date, last month etc

[Anonymous]

Hi

I am looking to take Individual bookings data that includes date booked, cost, number of passengers and group it by the following measures, yesterday, last week, month TD, September 21, Year TD etc. How do I take my indvidual bookings and group them by these measures?

Thanks

Graham

[Anonymous]

Thank you very much @BA_Pete, that is incredible

[BA_Pete]

No problem, happy time-intelligenceing (real word!).  🙂

[BA_Pete]

Hi @Anonymous ,

You need to create a calendar table, mark it as a date table, then relate calendar[date] to factTable[date].

Once you have that, you can add custom columns to it, such as [relativeDay], [relativeMonth], [relativeYear] etc.

This will also enable you to make use of Power BI's in-built time intelligence functions.

So, assuming a fully-equipped calendar table, you would complete your listed requirements by adding arguments in to CALCULATE measures something like this:

salesYesterday =
CALCULATE(
  SUM(factTable[Sales]),
  calendar[relativeDay] = -1
)

salesLastWeek =
CALCULATE(
  SUM(factTable[Sales]),
  calendar[relativeDay] IN {-1, -2, -3, -4, -5, -6, -7}  //rolling 7 days or:
  calendar[relativeWeek] = -1  //complete business week
)

salesMonthToDate =
CALCULATE(
  SUM(factTable[Sales]),
  DATESMTD(calendar[date])
)

salesYearToDate =
CALCULATE(
  SUM(factTable[Sales]),
  DATESYTD(calendar[date], "31/03")  //the '31/03' argument sets the end of year date you want to accumulate from
)

As you can see, all super-easy with a good calendar table.

Pete

[Anonymous]

Hi @BA_Pete 

Thank you very much for taking the time. Does this count as a calendar table?

I have this table, does this count as calendar table?

[BA_Pete · Accepted solution]

Hi @Anonymous ,

No, it doesn't. A proper calendar/date table must have a column that contains contiguous range of dates.

I've provided the M code to my own calendar table built in Power Query below. Copy the code and paste it over the default code in a new blank query.

It works on a financial year of 1st April to 31st March, and there's a few other things in there that may not be appropriate for your use, but hopefully it gives you a solid starting point to understand what we mean when we talk about calendar tables.

Pete

let
  // Declare acctChgDay variable
  acctChgDay = 1,
  // Define Date.Today
  Date.Today = Date.From(DateTime.LocalNow()),
  Source = { Number.From(#date(Date.Year(Date.AddYears(Date.Today+#duration(275,0,0,0),-20)),4,1))..Number.From(#date(Date.Year(Date.AddYears
(Date.Today+#duration(275,0,0,0),2)),3,31)) },
  convToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  chgDateType = Table.TransformColumnTypes(convToTable, {{"Column1", type date}}),
  renCols = Table.RenameColumns(chgDateType, {{"Column1", "date"}}),
  addDateKey = Table.AddColumn(renCols, "dateKey", each Date.ToText([date], "yyyMMdd"), type text),
  addWeekComm = Table.AddColumn(addDateKey, "weekComm", each Date.StartOfWeek([date], Day.Monday)),
  addWeekEnding = Table.AddColumn(addWeekComm, "weekEnding", each Date.EndOfWeek([date], Day.Monday)),
  addFinYear = Table.AddColumn(addWeekEnding, "finYear", each Date.Year([date]+#duration(275,0,0,0))),
  addFinYearText = Table.AddColumn(addFinYear, "finYearText", each Text.From([finYear]-1) & "/" & Text.End(Text.From([finYear]), 2)),
  addRelativeFY = Table.AddColumn(addFinYearText, "relativeFY", each [finYear] - Date.Year(Date.Today+#duration(275,0,0,0))),
  addFinPeriod = Table.AddColumn(addRelativeFY, "finPeriod", each if Date.Month([date]) >=4 then Date.Month([date])-3 else Date.Month([date])+9),
  addFinYearPeriod = Table.AddColumn(addFinPeriod, "finYearPeriod", each [finYear]*100+[finPeriod]),
  addFinWeekKey = Table.AddColumn(addFinYearPeriod, "finWeekKey", each Date.WeekOfYear(Date.AddWeeks([date], -13), Day.Monday)),
  addFinHY = Table.AddColumn(addFinWeekKey, "finHY", each if [finPeriod] >= 1 and [finPeriod] <= 6 then "H1" else "H2"),
  addFinQtr = Table.AddColumn(addFinHY, "finQtr", each if [finPeriod] >= 1 and [finPeriod] <= 3 then "Q1"
else if [finPeriod] >= 4 and [finPeriod] <= 6 then "Q2"
else if [finPeriod] >= 7 and [finPeriod] <= 9 then "Q3"
else "Q4"),
  addMonthKey = Table.AddColumn(addFinQtr, "monthKey", each Date.Month([date])),
  addMonth = Table.AddColumn(addMonthKey, "month", each Text.Start(Date.MonthName([date]), 3)),
  addMonthYear = Table.AddColumn(addMonth, "monthYear", each Text.Combine({[month], Text.End(Text.From(Date.Year
([date])),2)}, " ")),
  addCurrentMonth = Table.AddColumn(addMonthYear, "currentMonth", each if Date.Month(Date.Today) = Date.Month([date]) and Date.Year(Date.Today) = Date.Year([date]) then "CurrentMonth"
else if [date] < Date.StartOfMonth(Date.Today) then "History" else "Future"),
  addRelativeMonth = Table.AddColumn(addCurrentMonth, "relativeMonth", each (Date.Year([date]) * 12 + [monthKey]) - (Date.Year(Date.Today) * 12 + Date.Month(Date.Today))),
  addDayKey = Table.AddColumn(addRelativeMonth, "dayKey", each Date.DayOfWeek([date])),
  addDay = Table.AddColumn(addDayKey, "day", each Text.Start(Date.DayOfWeekName([date]), 3)),
  addRelativeDay = Table.AddColumn(addDay, "relativeDay", each [date] - Date.Today),
  addCurrentDay = Table.AddColumn(addRelativeDay, "currentDay", each if Date.Today = [date] then "CurrentDay"
else if [date] < Date.Today then "History" else "Future"),
  addDayType = Table.AddColumn(addCurrentDay, "dayType", each if [day] = "Sat" or [day] = "Sun" then "Weekend" else "Weekday"),
  addDayView = Table.AddColumn(addDayType, "dayView", each if [dayType] = "Weekend"
then null
else if [date] = Date.AddDays(Date.Today, -7)
then "OneWeekAgo"
else if [day] = "Fri" and [date] = Date.AddDays(Date.Today, -3)
then "LastWorkDay"
else if [date] = Date.AddDays(Date.Today, -1)
then "LastWorkDay"
else if [date] = Date.Today
then "Today"
else if [day] = "Mon" and [date] = Date.AddDays(Date.Today, 5)
then "NextWorkDay+3"
else if [day] = "Mon" and [date] = Date.AddDays(Date.Today, 4)
then "NextWorkDay+2"
else if [day] = "Mon" and [date] = Date.AddDays(Date.Today, 3)
then "NextWorkDay+1"
else if [day] = "Tue" and [date] = Date.AddDays(Date.Today, 5)
then "NextWorkDay+3"
else if [day] = "Tue" and [date] = Date.AddDays(Date.Today, 4)
then "NextWorkDay+2"
else if [day] = "Wed" and [date] = Date.AddDays(Date.Today, 5)
then "NextWorkDay+3"
else if [date] = Date.AddDays(Date.Today, 1)
then "NextWorkDay+1"
else if [date] = Date.AddDays(Date.Today, 2)
then "NextWorkDay+2"
else if [date] = Date.AddDays(Date.Today, 3)
then "NextWorkDay+3"
else null),
  addCurrentPdAccts = Table.AddColumn(addDayView, "currentPdAccts", each if
(Date.IsInPreviousMonth([date]) and Date.Day(Date.Today) < acctChgDay)
or
(Date.Month([date]) = Date.Month(Date.Today) and Date.Year(Date.Today) = Date.Year([date]) and Date.Day(Date.Today) < acctChgDay)
then "Current Pd"
else
if
(Date.Month([date]) = Date.Month(Date.Today) and Date.Year(Date.Today) = Date.Year([date]) and Date.Day(Date.Today) >= acctChgDay)
then "Current Pd"
else
if
[date] < Date.Today
then "History"
else "Future"),
  addPeriodTypeAccts = Table.AddColumn(addCurrentPdAccts, "periodTypeAccts", each if [currentPdAccts] = "History" then "Actual" else "Forecast"),
  chgAllTypes = Table.TransformColumnTypes(addPeriodTypeAccts, {{"date", type date}, {"finYear", type text}, {"finPeriod", Int64.Type}, {"finYearPeriod", Int64.Type}, {"finHY", type text}, {"finQtr", type text}, {"month", type text}, {"relativeFY", type text}, {"currentPdAccts", type text}, {"periodTypeAccts", type text}, {"monthYear", type text}, {"dayKey", Int64.Type}, {"day", type text}, {"monthKey", Int64.Type}, {"dayView", type text}, {"dayType", type text}, {"finWeekKey", Int64.Type}, {"currentMonth", type text}, {"relativeMonth", Int64.Type}, {"currentDay", type text}, {"weekComm", type date}, {"weekEnding", type date}, {"relativeDay", Int64.Type}, {"finYearText", type text}})
in
  chgAllTypes