Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Proud to be a Datanaut!
No problem, happy time-intelligenceing (real word!). 🙂
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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?
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
Proud to be a Datanaut!