date function
let CreateDateTable = (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]), type number),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date]), type number),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type number),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date]), type number),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], type number),
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]) & " " & Number.ToText([Year]), type text) ,
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), type text),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date]), type number),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
#"Inserted Start of Month" = Table.AddColumn(InsertWeekEnding, "StartOfMonth", each Date.StartOfMonth([Date]), type date)
in
#"Inserted Start of Month"
in
CreateDateTable
R code ggplot easy
# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script:
# dataset <- data.frame(StartOfMonth, Revenue)
# dataset <- unique(dataset)
# Paste or type your script code here:
library(ggplot2)
dataset$date = as.Date(dataset$StartOfMonth)
graphic <- dataset
ggplot(data=graphic, aes(x=graphic$date , y=graphic$sales))+geom_line(color = "#FC4E07", size = 2)
R code ggplot facet_grid
# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script:
# dataset <- data.frame(StartOfMonth, Revenue, Category)
# dataset <- unique(dataset)
# Paste or type your script code here:
library(ggplot2)
dataset$date = as.Date(dataset$StartOfMonth)
graphic <- dataset
ggplot(data=graph, aes(x=graph$date , y=graph$sales))+geom_line(color = "#FC4E07", size = 2)+facet_grid(Segment~Category)
R Code with Arima Forecast
# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script:
# dataset <- data.frame(StartOfMonth, Revenue)
# dataset <- unique(dataset)
# Paste or type your script code here:
dataset$date = as.Date(dataset$StartOfMonth)
graphic <- dataset
library(forecast)
library(tseries)
ARIMA_Sales <- ts(dataset$sales,start=c(1))
Sales_Forecast<- auto.arima(ARIMA_Sales, seasonal=TRUE)
Predicted_Sales <- forecast(Sales_Forecast, h=12)
plot(Predicted_Sales)