Tidval – Power Query

En tabell med olika perioder är smidigt att ha som grund för snabbval i sin rapport, exempelvis YTD, Föregående månad, R12 etc. Nedan är ett exempel på ett sådant skript. Koppla ihop denna tabell med tidsdimensionen och sätt dubbelriktad filtrering i datamodellen.

let
 
StartCD = Date.From(DateTime.LocalNow()),
EndCD = Date.From(DateTime.LocalNow()),
DaysCD = Duration.Days(Duration.From(EndCD-StartCD))+1,
TextCD = "Idag",

StartCM = Date.StartOfMonth(Date.From(DateTime.LocalNow())),
EndCM = Date.EndOfMonth(Date.From(DateTime.LocalNow())),
DaysCM = Duration.Days(Duration.From(EndCM-StartCM))+1,
TextCM = "Innevarande månad",
 
StartLM = Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-1)),
EndLM = Date.EndOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-1)),
DaysLM = Duration.Days(Duration.From(EndLM-StartLM))+1,
TextLM = "Föregående månad",
 
StartCW = Date.StartOfWeek(Date.From(DateTime.LocalNow()),1),
EndCW = Date.EndOfWeek(Date.From(DateTime.LocalNow()),1),
DaysCW = 7,
TextCW = "Innevarande vecka",
 
StartLW = Date.StartOfWeek(Date.AddDays(Date.From(DateTime.LocalNow()),-7),1),
EnLCW = Date.EndOfWeek(Date.AddDays(Date.From(DateTime.LocalNow()),-7),1),
DaysLW = 7,
TextLW = "Föregående vecka",
 
StartCY = Date.StartOfYear(Date.From(DateTime.LocalNow())),
EndCY = Date.EndOfYear(Date.From(DateTime.LocalNow())),
DaysCY = Duration.Days(Duration.From(EndCY-StartCY))+1,
TextCY = "Innevarande år",
 
StartLY = Date.StartOfYear(Date.AddYears(Date.From(DateTime.LocalNow()),-1)),
EndLY = Date.EndOfYear(Date.AddYears(Date.From(DateTime.LocalNow()),-1)),
DaysLY = Duration.Days(Duration.From(EndLY-StartLY))+1,
TextLY = "Föregående år",
 
StartR12 = Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-12)),
EndR12 = Date.EndOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-1)),
DaysR12 = Duration.Days(Duration.From(EndR12-StartR12))+1,
TextR12 = "R12",
 
StartR3 = Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-3)),
EndR3 = Date.EndOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-1)),
DaysR3 = Duration.Days(Duration.From(EndR3-StartR3))+1,
TextR3 = "R3",
 
StartCMLY = Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-12)),
EndCMLY = Date.EndOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-12)),
DaysCMLY = Duration.Days(Duration.From(EndCMLY-StartCMLY))+1,
TextCMLY = "Innevarande månad föregående år",
 
StartYTD = Date.StartOfYear(Date.From(DateTime.LocalNow())),
EndYTD = Date.From(DateTime.LocalNow()),
DaysYTD = Duration.Days(Duration.From(EndYTD-StartYTD))+1,
TextYTD = "YTD",

StartMTD = Date.StartOfMonth(Date.From(DateTime.LocalNow())),
EndMTD = Date.From(DateTime.LocalNow()),
DaysMTD = Duration.Days(Duration.From(EndMTD-StartMTD))+1,
TextMTD = "MTD",
 
StartYTDLY = Date.StartOfYear(Date.AddYears(Date.From(DateTime.LocalNow()),-1)),
EndYTDLY = Date.AddMonths(Date.From(DateTime.LocalNow()),-12),
DaysYTDLY = Duration.Days(Duration.From(EndYTDLY-StartYTDLY))+1,
TextYTDLY = "YTD föregående år",

StartROY = Date.StartOfMonth(Date.From(DateTime.LocalNow())),
EndROY = Date.EndOfYear(Date.From(DateTime.LocalNow())),
DaysROY = Duration.Days(Duration.From(EndROY-StartROY))+1,
TextROY = "Resten av året",

SourceCD=List.Dates(StartCD,DaysCD,#duration(1,0,0,0)),
CurrentDay= Table.FromList(SourceCD,Splitter.SplitByNothing(),{"Date"}),
ChangeTypeCD = Table.TransformColumnTypes(CurrentDay,{{"Date", type date}}),
PeriodCD= Table.AddColumn(ChangeTypeCD ,"Period",each TextCD,type text),

 
SourceCM=List.Dates(StartCM,DaysCM,#duration(1,0,0,0)),
CurrentMonth= Table.FromList(SourceCM,Splitter.SplitByNothing(),{"Date"}),
ChangeTypeCM = Table.TransformColumnTypes(CurrentMonth,{{"Date", type date}}),
PeriodCM= Table.AddColumn(ChangeTypeCM ,"Period",each TextCM,type text),
 
SourceLM = List.Dates(StartLM,DaysLM,#duration(1,0,0,0)),
LastMonth= Table.FromList(SourceLM,Splitter.SplitByNothing(),{"Date"}),
ChangeTypeLM = Table.TransformColumnTypes(LastMonth,{{"Date", type date}}),
PeriodLM= Table.AddColumn(ChangeTypeLM ,"Period",each TextLM,type text),
 
SourceCW = List.Dates(StartCW,DaysCW,#duration(1,0,0,0)),
CurrentWeek= Table.FromList(SourceCW,Splitter.SplitByNothing(),{"Date"}),
ChangeTypeCW = Table.TransformColumnTypes(CurrentWeek,{{"Date", type date}}),
PeriodCW= Table.AddColumn(ChangeTypeCW ,"Period",each TextCW,type text),
 
SourceLW = List.Dates(StartLW,DaysLW,#duration(1,0,0,0)),
LastWeek= Table.FromList(SourceLW,Splitter.SplitByNothing(),{"Date"}),
ChangeTypeLW = Table.TransformColumnTypes(LastWeek,{{"Date", type date}}),
PeriodLW= Table.AddColumn(ChangeTypeLW ,"Period",each TextLW,type text),
 
SourceCY = List.Dates(StartCY,DaysCY,#duration(1,0,0,0)),
CurrentYear = Table.FromList(SourceCY,Splitter.SplitByNothing(),{"Date"}),
ChangeTypeCY = Table.TransformColumnTypes(CurrentYear,{{"Date", type date}}),
PeriodCY = Table.AddColumn(ChangeTypeCY ,"Period",each TextCY,type text),
 
SourceLY = List.Dates(StartLY,DaysLY,#duration(1,0,0,0)),
LastYear= Table.FromList(SourceLY,Splitter.SplitByNothing(),{"Date"}),
ChangeTypeLY = Table.TransformColumnTypes(LastYear,{{"Date", type date}}),
PeriodLY= Table.AddColumn(ChangeTypeLY ,"Period",each TextLY,type text),
 
SourceR12 = List.Dates(StartR12,DaysR12,#duration(1,0,0,0)),
R12= Table.FromList(SourceR12,Splitter.SplitByNothing(),{"Date"}),
ChangeTypeR12 = Table.TransformColumnTypes(R12,{{"Date", type date}}),
PeriodR12= Table.AddColumn(ChangeTypeR12 ,"Period",each TextR12,type text),
 
 
SourceR3 = List.Dates(StartR3,DaysR3,#duration(1,0,0,0)),
R3= Table.FromList(SourceR3,Splitter.SplitByNothing(),{"Date"}),
ChangeTypeR3 = Table.TransformColumnTypes(R3,{{"Date", type date}}),
PeriodR3= Table.AddColumn(ChangeTypeR3 ,"Period",each TextR3,type text),
 
SourceCMLY=List.Dates(StartCMLY,DaysCMLY,#duration(1,0,0,0)),
CurrentMonthLastYear= Table.FromList(SourceCMLY,Splitter.SplitByNothing(),{"Date"}),
ChangeTypeCMLY = Table.TransformColumnTypes(CurrentMonthLastYear,{{"Date", type date}}),
PeriodCMLY= Table.AddColumn(ChangeTypeCMLY ,"Period",each TextCMLY,type text),
 
 
SourceYTD=List.Dates(StartYTD,DaysYTD,#duration(1,0,0,0)),
YearToDate= Table.FromList(SourceYTD,Splitter.SplitByNothing(),{"Date"}),
ChangeTypeYTD = Table.TransformColumnTypes(YearToDate,{{"Date", type date}}),
PeriodYTD= Table.AddColumn(ChangeTypeYTD ,"Period",each TextYTD,type text),

SourceMTD=List.Dates(StartMTD,DaysMTD,#duration(1,0,0,0)),
MonthToDate= Table.FromList(SourceMTD,Splitter.SplitByNothing(),{"Date"}),
ChangeTypeMTD = Table.TransformColumnTypes(MonthToDate,{{"Date", type date}}),
PeriodMTD= Table.AddColumn(ChangeTypeMTD ,"Period",each TextMTD,type text),
 
SourceYTDLY=List.Dates(StartYTDLY,DaysYTDLY,#duration(1,0,0,0)),
YearToDateLastYear= Table.FromList(SourceYTDLY,Splitter.SplitByNothing(),{"Date"}),
ChangeTypeYTDLY = Table.TransformColumnTypes(YearToDateLastYear,{{"Date", type date}}),
PeriodYTDLY= Table.AddColumn(ChangeTypeYTDLY ,"Period",each TextYTDLY,type text),
 
SourceROY=List.Dates(StartROY,DaysROY,#duration(1,0,0,0)),
RestOfYear= Table.FromList(SourceROY,Splitter.SplitByNothing(),{"Date"}),
ChangeTypeROY = Table.TransformColumnTypes(RestOfYear,{{"Date", type date}}),
PeriodROY= Table.AddColumn(ChangeTypeROY ,"Period",each TextROY,type text), 
 
Period=Table.Combine(
{
PeriodCD,
PeriodLM,  
PeriodCM,
PeriodCW,
PeriodLW,
PeriodCY,
PeriodLY,
PeriodR12,
PeriodR3,
PeriodCMLY,
PeriodYTD,
PeriodMTD,
PeriodYTDLY,
PeriodROY}),
    #"Renamed Columns" = Table.RenameColumns(Period,{{"Period", "Val"}, {"Date", "Datum"}})
in
    #"Renamed Columns"