Hvorfor skal man have en datotabel?
Hvis man er gået i gang med at lave sine første datamodeller i Power BI, løber man hurtigt ind i et behov for en datotabel. Man skal have en datotabel for at kunne lave beregninger ”over tid” i DAX.
Hvad er en datotabel?
En datotabel eller en datodimensionstabel er en tabel med en ubrudt række af datoer.
Vores datotabel har vi sammensat med inspiration fra andre gode datotabeller, man kan finde på nettet, samt ud fra konkrete kundeønsker, vi har mødt som konsulenter.
”Vi vil gerne kunne sammenligne salget i uge 7 sidste år med uge 7 i år”
Hvem har brug for en datotabel i Power BI?
Denne blog post henvender sig til dig, der ikke har et Data Ware House, en kube med dine data eller en BI afdeling til at hjælpe dig med dine datamodeller og rapporter.
Du er selv gået i gang med Power BI, og har brug for nogle udregninger eller filtre i din rapport med ”tidsintelligens”/Time Intelligence.
”Vi vil kun se data fra dette og sidste finansår”
(Næst)Best Practise
Fremgangsmåden i denne artikel er nemlig IKKE best practise.
Best practise er at placere en datotabel så tæt på datakilden som muligt. F.eks. i en SQL tabel, i et datawarehouse eller i en kube.
Men hvis man ikke kan eller har adgang til det – så må man gå med den næstbedste løsning, og det er at lave en datotabel i sproget PowerQuery (M) i datamodellen.
Sådan gør du
For at bruge vores datotabel, skal du enten downloade vores rapport med tabellen i eller kopiere al koden i den grå boks. Begge dele finder du nederst på denne side.
Hvis du vælger at bruge koden i din egen rapport, skal du kopiere det hele fra den grå boks og:
- Så skal du åbne din Power BI rapport.
- Så skal du klikke på transformer data.
- Så skal du klikke på Ny kilde
- Vælg Tom forespørgsel
- Tryk på Avanceret editor
- Marker alt (ctrl + a)
- Sæt ind (ctrl + v)
- Tryk Udført
- Omdøb Forespørgslen til DateDIM
Nu skal du klikke på nogle af de anvendte trin i forespørgslen.
- Du skal klikke på FromYear og angive, hvor langt tilbage i fortiden din datotabel skal begynde. Her bør du vælge det tidligste år, hvor dine data er fra.
- Du skal klikke på EndPoint og angive, hvor mange år fra det aktuelle år slutåret skal være.
- Du skal klikke på StartFiscalYear og angive, hvilken måned, der er den første i jeres finansår. Hvis jeres finansår følger kalenderåret, skriver du 1. Hvis jeres finansår starter 1. maj, skriver du 5.
- Du skal klikke på FirstDayOfWeek og angive hvilken dag, der er den første i ugen. For de fleste i Danmark er det mandag, men det kan jo være, at du skal rapportere på noget data fra et land, der betragter søndag som den første dag i ugen.
- Nu er din datotabel klar til brug, og du kan klikke på Luk og Anvend.
Gør dine datoer klar til brug
Hvis du – som os – oftest bygger rapporter på Dynamics 365 data, skal du lige lave lidt arbejde i din datamodel, før du kan få det fulde udbytte af din datotabel.
I Dynamics 365 er datoer nemlig oftest gemt i et felt, der indeholder både dato og tid.
Du skal udtrække den rene datoværdi fra dine datokolonner, f.eks. Oprettet.
Derved får du en ny kolonne med din Oprettet dato, og det er den, du skal bruge i dine relationer.
- Du markerer din datokolonne, der også indeholder klokkeslæt.
- Tryk på Fanen Transformer
- Tryk på Dato
- Vælg ”Kun dato”
Du får nu et nyt trin i din forespørgsel, hvor din transformation ser sådan ud i PowerQuery (M):
= Table.TransformColumns(DetSenesteTrinIDinForespørgsel,{{"Oprettet", DateTime.Date, type date}})Relationer
Når du har gjort dette for alle dine datokolonner – i alle dine tabeller, der indeholder datoer, så trykker du på Luk og Anvend.
Nu skal du opsætte dine relationer. Datotabellens kolonne med Date skal forbindes til alle dine kolonner med datoer.
Relationer opsættes i Model lærredet
Opsæt dine relationer i Model lærredet
Scenarie 1: Kun en dato i din tabel
Din tabel indeholder kun en kolonne med dato. Det kan f.eks. være en salgstabel, hvor den eneste kolonne er en dato for selve salget.
Her trækker du bare Date over i Salgsdato (med musen) og giver slip.
Power BI vil komme med sit bedste bud på, hvilken relationstype det skal være. Det skal være en ”En-til-mange” relation fra DateDIM til Salgstabellen – og filtreringen skal være ”Enkelt”. Hvis Power BI ikke selv fandt frem til dette, skal du rette det til. (Pilen skal gå fra DateDIM til din salgstabel, og der skal være et 1-tal ved DateDIM og en * ved salgstabellen) Nu bør du skjule Salgsdato feltet i din salgstabel.
Fremover skal du kun bruge Date feltet fra DateDIM tabellen, når du vil vise noget eller beregne noget ”over tid” fra din salgstabel. Ved at skjule den originale Salgsdato kommer du ikke til at forvirre dig selv eller dine rapportbrugere.
Scenarie 2: Mere end en datokolonne i din fact tabel
Din tabel indeholder mere end en kolonne med dato. Det kan f.eks. være en salgsmuligheder i CRM, hvor der er:
- En oprettet dato, du gerne vil bruge, når du skal beregne, hvor lang levetid salgsmulighederne har.
- En anslået afslutningsdato, du gerne vil bruge, når du skal vise, hvornår I forventer at lukke jeres salg.
- En faktisk lukkedato, du gerne vil bruge, når du skal vise, hvornår I lukkede allerede vundne/tabte salgsmuligheder.
- En seneste aktivitetsdato, du gerne vil bruge, når du skal vise, hvornår der sidst har været aktivitet på jeres åbne salgsmuligheder.
Der er ikke en af datoerne, der er mere ”rigtig” end de andre, og du vil gerne rapportere og lave beregninger med alle datoerne.
Løsningen er ikke at lave ekstra datotabeller, men i stedet at lave inaktive relationer mellem DateDIM og alle datokolonnerne – og så bruge disse relationer i dine beregninger.
Den første relation, du laver, vil være aktiveret. De andre vil være deaktiverede. Du skal derfor ind og deaktivere den første relation, du lavede.
Brug datotabellen i et DAX udtryk og udregn Anslået Omsætning
Når du nu har lavet dit forarbejde med datotabellen og de enkelte datokolonner, kan du sætte det hele sammen.
Nu har jeg fået alle sælgerne til at indtaste deres salgsmuligheder i CRM. Men hvornår kan vi forvente, at de får lukket deres salg? Hvor mange penge forventer de at omsætte for?
– Anonym salgschef
For at udregne ”Anslået omsætning”, skal du have fat i dine salgsmuligheder, anslået omsætning, status for salgsmuligheden og den anslåede lukkedato. Beregningen viser, hvornår I forventer, at jeres salg kommer ud af pipelinen.
USERELATIONSHIP bruges til at aktivere den inaktive relation, du lavede mellem Date og Anslået lukkedato.
Anslået omsætning åbne salgsmuligheder =
CALCULATE (
SUM ( Salgsmuligheder[Anslået omsætning] ),
Salgsmuligheder[Status] = “Åben”,
USERELATIONSHIP ( Salgsmuligheder[Anslået lukkedato], DateDIM[Date] )
)
Datotabellen som filter
Du kan bruge de mange kolonner i datotabellen som filtre – både i DAX beregninger – og i selve rapporten.
Her har vi trukket CurrentQuarter ind som sidefilter, og alle beregninger og visuals på denne side vil dermed blive begrænset til dette kvartal. Vi anbefaler, at filtre placeres i sidepanelet, og at du skriver en tydelig overskrift på din rapportside, når du bruger sidefiltre.
ISO uger
Det med at bruge ugenumre er noget, vi er meget glade for i Danmark. Vores datotabel indeholder ugenumrene i kolonnen ISOWeek. Det kan man bruge til beregninger, der sammenligner salg år over år fordelt pr uge.
Hvad solgte vi for i uge 7 sidste år?
– Anonym CFO
Kopier koden
Til dig, der allerede har en rapport klar, hvor datotabellen skal bruges. Kopier al koden i den grå boks og følg vejledningen.
let
/*
****This Calendar was created and provided by Donkey Power****
****This can be freely shared and used as long as this text comment is retained.****
https://donkeypower.dk
https://donkeypower.dk/2022/02/15/datotabel-i-power-bi-med-iso-uger/
*/
// configurations start
Now = Date.Year(Date.From(DateTime.LocalNow())), // today's date
FromYear = 2021, // set the start year of the date dimension. Dates start from 1st of January of this year
EndPoint = 1, // set the running end year of the date dimension. Current year + amount of years until end year. Dates end at 31st of December of this year
StartofFiscalYear = 5, // set the month number that is start of the financial year. Example: if fiscal year start is May, value is 5
FirstDayOfWeek = Day.Monday, // set the week's start day, values: Day.Monday, Day.Sunday....
// configuration end
NextYear = Now + EndPoint,
Source = List.Dates(
#date(FromYear, 1, 1),
Duration.Days(#date(NextYear, 12, 31) - #date(FromYear, 1, 1)) + 1,
#duration(1, 0, 0, 0)
),
#"Converted to Table" = Table.FromList(
Source,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Renamed Date" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),
#"Date Type Date" = Table.TransformColumnTypes(#"Renamed Date", {{"Date", type date}}),
IsInPreviousMonth = Table.AddColumn(
#"Date Type Date",
"IsInPreviousMonth",
each Date.IsInPreviousMonth([Date]),
type logical
),
Year = Table.AddColumn(IsInPreviousMonth, "Year", each Date.Year([Date]), Int64.Type),
QuarterNumber = Table.AddColumn(Year, "QuarterNumber", each Date.QuarterOfYear([Date]), Int64.Type),
FirstYear = List.First(QuarterNumber[Year]),
QuarterIndex = Table.AddColumn(
QuarterNumber,
"QuarterIndex",
each
let
Mult = [Year] - FirstYear,
Index = 4 * Mult + [QuarterNumber]
in
Index
, Int64.Type),
Quarter = Table.AddColumn(
QuarterIndex,
"Quarter",
each Number.ToText([Year]) & " Q" & Number.ToText([QuarterNumber]),
type text
),
MonthNumber = Table.AddColumn(Quarter, "MonthNumber", each Date.Month([Date]), Int64.Type),
MonthIndex = Table.AddColumn(
MonthNumber,
"MonthIndex",
each
let
Mult = [Year] - FirstYear,
Index = 12 * Mult + [MonthNumber]
in
Index
, Int64.Type),
Month = Table.AddColumn(MonthIndex, "Month", each Date.ToText([Date], "yyyy MMM"), type text),
MonthName = Table.AddColumn(Month, "MonthName", each Date.ToText([Date], "MMMM"), type text),
MonthNameShort = Table.AddColumn(MonthName, "MonthNameShort", each Date.ToText([Date], "MMM"), type text),
WeekNumber = Table.AddColumn(MonthNameShort, "WeekNumber", each Date.WeekOfYear([Date]), Int64.Type),
WeekIndex = Table.AddColumn(
WeekNumber,
"WeekIndex",
each
let
Mult = [Year] - FirstYear,
Index = 53 * Mult + [WeekNumber]
in
Index
, Int64.Type),
Week = Table.AddColumn(
WeekIndex,
"Week",
each Number.ToText([Year]) & " W" & Number.ToText([WeekNumber], "00"),
type text
),
DayOfYear = Table.AddColumn(
Week,
"DayOfYear",
each
let
Leap = Date.IsLeapYear([Date]),
DOY = Date.DayOfYear([Date])
in
if not Leap and [Date] >= #date([Year], 3, 1) then DOY + 1 else DOY
, Int64.Type),
DayOfQuarter = Table.AddColumn(
DayOfYear,
"DayOfQuarter",
each
let
Leap = Date.IsLeapYear([Date]),
DOQ = Duration.Days([Date] - Date.StartOfQuarter([Date])) + 1
in
if not Leap and [QuarterNumber] = 1 and [Date] >= #date([Year], 3, 1) then DOQ + 1 else DOQ
, Int64.Type),
DayOfMonth = Table.AddColumn(DayOfQuarter, "DayOfMonth", each Date.Day([Date]), Int64.Type),
DayOfWeek = Table.AddColumn(DayOfMonth, "DayOfWeek", each Date.DayOfWeek([Date], Day.Monday) + 1, Int64.Type),
DayName = Table.AddColumn(DayOfWeek, "DayName", each Date.ToText([Date], "dddd"), type text),
DayNameShort = Table.AddColumn(DayName, "DayNameShort", each Date.ToText([Date], "ddd"), type text),
Weekday = Table.AddColumn(
#"DayNameShort",
"Weekday",
each if [DayName] = "lørdag" or [DayName] = "søndag" then "Weekend" else "Weekday",
type text
),
WeekdayFlag = Table.AddColumn(Weekday, "WeekdayFlag", each [Weekday] = "Weekday", type logical),
CurrentYear = Table.AddColumn(WeekdayFlag, "CurrentYear", each Date.IsInCurrentYear([Date]), type logical),
CurrentQuarter = Table.AddColumn(
CurrentYear,
"CurrentQuarter",
each Date.IsInCurrentQuarter([Date]),
type logical
),
CurrentMonth = Table.AddColumn(CurrentQuarter, "CurrentMonth", each Date.IsInCurrentMonth([Date]), type logical),
CurrentWeek = Table.AddColumn(CurrentMonth, "CurrentWeek", each Date.IsInCurrentWeek([Date]), type logical),
Today = Table.AddColumn(CurrentWeek, "Today", each [Date] = DateTime.Date(DateTime.LocalNow()), type logical),
CurrentYTD = Table.AddColumn(Today, "CurrentYTD", each Date.IsInYearToDate([Date]), type logical),
CurrentQTD = Table.AddColumn(
CurrentYTD,
"CurrentQTD",
each [CurrentQuarter] and [Date] <= DateTime.Date(DateTime.LocalNow()),
type logical
),
CurrentMTD = Table.AddColumn(
CurrentQTD,
"CurrentMTD",
each [CurrentMonth] and [Date] <= DateTime.Date(DateTime.LocalNow()),
type logical
),
CurrentWTD = Table.AddColumn(
CurrentMTD,
"CurrentWTD",
each [CurrentWeek] and [Date] <= DateTime.Date(DateTime.LocalNow()),
type logical
),
ISOWeek = Table.AddColumn(
CurrentWTD,
"ISOWeek",
each
if Number.RoundDown(
(Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7
)
= 0
then
Number.RoundDown(
(
Date.DayOfYear(#date(Date.Year([Date]) - 1, 12, 31))
- (Date.DayOfWeek(#date(Date.Year([Date]) - 1, 12, 31), Day.Monday) + 1)
+ 10
)
/ 7
)
else if (
Number.RoundDown(
(Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7
)
= 53 and (Date.DayOfWeek(#date(Date.Year([Date]), 12, 31), Day.Monday) + 1 < 4)
)
then
1
else
Number.RoundDown(
(Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7
)
, Int64.Type),
#"Inserted End of Month" = Table.AddColumn(
ISOWeek,
"End of Month",
each Date.Day(Date.EndOfMonth([Date])),
Int64.Type
),
PercentOfMonth = Table.AddColumn(
#"Inserted End of Month",
"PercentOfMonth",
each [DayOfMonth] / [End of Month],
Percentage.Type
),
WeekOffset = Table.AddColumn(
PercentOfMonth,
"WeekOffset",
each (Number.From((Date.StartOfWeek([Date])-Date.StartOfWeek(DateTime.Date(DateTime.LocalNow()))))/7),
Int64.Type
),
FiscalYear = Table.AddColumn(
WeekOffset,
"FiscalYear",
each
if Date.Month([Date]) >= StartofFiscalYear then
Date.Year([Date])
else
Date.Year([Date]) - 1
, Int64.Type
),
FiscalMonth = Table.AddColumn(
FiscalYear,
"FiscalMonth",
each
let
MonthOffset = Date.Month([Date]) - StartofFiscalYear + 1
in
if MonthOffset < 1 then MonthOffset + 12 else MonthOffset
, Int64.Type
),
FiscalDayOfYear = Table.AddColumn(
FiscalMonth,
"FiscalDayOfYear",
each
let
FiscalYearStart = if Date.Month([Date]) >= StartofFiscalYear then #date(Date.Year([Date]), StartofFiscalYear, 1) else #date(Date.Year([Date]) - 1, StartofFiscalYear, 1)
in
Number.From([Date]) - Number.From(FiscalYearStart) + 1,
Int64.Type
),
FiscalQuarter = Table.AddColumn(
FiscalDayOfYear,
"FiscalQuarter",
each "FQ" & Number.ToText(Number.RoundDown(([FiscalMonth] - 1) / 3) + 1),
type text
),
FiscalYearName = Table.AddColumn(
FiscalQuarter,
"FiscalYearName",
each
let
FiscalStartYear = if Date.Month([Date]) >= StartofFiscalYear then Date.Year([Date]) else Date.Year([Date]) - 1,
FiscalEndYear = FiscalStartYear + 1
in
"FY " & Text.From(FiscalStartYear) & "-" & Text.End(Text.From(FiscalEndYear), 4),
type text
),
#"Sorted Rows" = Table.Sort(FiscalYearName,{{"Date", Order.Ascending}})
in
#"Sorted Rows"Lad Donkey Power gøre det meste af arbejdet og download en Power BI-rapport.
Hvis ovenstående lige var lidt for meget kode, og du bare gerne vil have en knap at trykke på, så brug Download knappen nedenfor. Du skal selv unzippe .pbix filen, men ellers er det hele serveret.
Antal downloads: 138
Lad Donkey Power gøre mere af arbejdet
- Er du godt i gang med Power BI, men gået lidt i stå?
- Er du slet ikke i gang og har den kæmpe grå boks med kode skræmt dig væk fra at begynde selv?
- Vil du gerne i gang med Power BI?
Vi er allerbedst til Power BI rapportering på Dynamics 365 Customer Engagement data, men har også erfaring med NAV, C5, MySQL, SQL, Google Analytics og selvfølgelig Excel. Hvis det er data, og det er nogenlunde struktureret, skal vi nok finde ud af det sammen.
Vi vil rigtig gerne hjælpe dig i gang/videre/helt i mål. Du kan kontakte os på ml@donkeypower.dk eller 28729601.
Har du spørgsmål til Power BI, kommentarer eller vil du blot rose os for vores kode, så skriv en kommentar herunder.
