Skip to main content

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.

Anvendte trin for Power Query forespørgslen
Angiv, hvilket år datotabellen skal begynde.
  • 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å ToYear og angive, hvor langt ud i fremtiden, din datotabel skal række.
  • 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.

Udtræk Kun dato fra kolonnen Oprettet, så du har datoen uden klokkeslet
  • 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.

Kun een dato i fact tabellen
Træk Date til Salgsdato i fact tabellen

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.

Tryk på øjet for at skjule kolonnen

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.

Fjern fluebenet i Aktivér denne relation

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.

CurrentQuarter som sidefilter

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.

Ugenumre

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.

//Date table from www.donkeypower.dk
let
  // configurations start                                                                     
  Now = 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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  ToYear = 2025,  // set the end year of the date dimension. 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                                                            
  Source = List.Dates(
    #date(FromYear, 1, 1), 
    Duration.Days(#date(ToYear, 12, 31) - #date(FromYear, 1, 1)) + 1, 
    #duration(1, 0, 0, 0)
  ), 
  #"Converted to Table" = Table.FromList(
    Source, 
    Splitter.SplitByNothing(), 
    null, 
    null, 
    ExtraValues.Error
  ), 
  FiscalMonthBaseIndex = 13 - StartofFiscalYear, 
  AdjustedFiscalMonthBaseIndex = 
    if (FiscalMonthBaseIndex >= 12 or FiscalMonthBaseIndex < 0) then
      0
    else
      FiscalMonthBaseIndex, 
  #"Renamed Date" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}), 
  #"Date Type Date" = Table.TransformColumnTypes(#"Renamed Date", {{"Date", type date}}), 
  FiscalBaseDate = Table.AddColumn(
    #"Date Type Date", 
    "FiscalBaseDate", 
    each Date.AddMonths([Date], AdjustedFiscalMonthBaseIndex)
  ), 
  Year = Table.AddColumn(FiscalBaseDate, "Year", each Date.Year([Date])), 
  FiscalYear = Table.AddColumn(Year, "FiscalYear", each Date.Year([FiscalBaseDate])), 
  FY = Table.AddColumn(FiscalYear, "FY", each "FY" & Number.ToText([FiscalYear]), type text), 
  StartOfYear = Table.AddColumn(FY, "StartOfYear", each Date.StartOfYear([Date]), type date), 
  QuarterNumber = Table.AddColumn(StartOfYear, "QuarterNumber", each Date.QuarterOfYear([Date])), 
  FirstYear = List.First(QuarterNumber[Year]), 
  QuarterIndex = Table.AddColumn(
    QuarterNumber, 
    "QuarterIndex", 
    each 
      let
        Mult  = [Year] - FirstYear, 
        Index = 4 * Mult + [QuarterNumber]
      in
        Index
  ), 
  Quarter = Table.AddColumn(
    QuarterIndex, 
    "Quarter", 
    each Number.ToText([Year]) & " Q" & Number.ToText([QuarterNumber]), 
    type text
  ), 
  FiscalQuarter = Table.AddColumn(
    Quarter, 
    "FiscalQuarter", 
    each Date.QuarterOfYear([FiscalBaseDate])
  ), 
  StartOfQuarter = Table.AddColumn(
    FiscalQuarter, 
    "StartOfQuarter", 
    each Date.StartOfQuarter([Date]), 
    type date
  ), 
  EndOfQuarter = Table.AddColumn(
    StartOfQuarter, 
    "EndOfQuarter", 
    each Date.EndOfQuarter([Date]), 
    type date
  ), 
  MonthNumber = Table.AddColumn(EndOfQuarter, "MonthNumber", each Date.Month([Date])), 
  MonthIndex = Table.AddColumn(
    MonthNumber, 
    "MonthIndex", 
    each 
      let
        _Multiply = [Year] - FirstYear, 
        Index     = 12 * _Multiply + [MonthNumber]
      in
        Index
  ), 
  Month = Table.AddColumn(MonthIndex, "Month", each Date.ToText([Date], "yyyy MMM"), type text), 
  FiscalMonth = Table.AddColumn(Month, "FiscalMonth", each Date.Month([FiscalBaseDate])), 
  RemoveFiscalBaseDate = Table.RemoveColumns(FiscalMonth, {"FiscalBaseDate"}), 
  MonthName = Table.AddColumn(
    RemoveFiscalBaseDate, 
    "MonthName", 
    each Date.ToText([Date], "MMMM"), 
    type text
  ), 
  MonthNameShort = Table.AddColumn(
    MonthName, 
    "MonthNameShort", 
    each Date.ToText([Date], "MMM"), 
    type text
  ), 
  StartOfMonth = Table.AddColumn(
    MonthNameShort, 
    "StartOfMonth", 
    each Date.StartOfMonth([Date]), 
    type date
  ), 
  EndOfMonth = Table.AddColumn(StartOfMonth, "EndOfMonth", each Date.EndOfMonth([Date]), type date), 
  DaysInMonth = Table.AddColumn(
    EndOfMonth, 
    "DaysInMonth", 
    each Date.DaysInMonth([Date]), 
    Int64.Type
  ), 
  WeekNumber = Table.AddColumn(
    DaysInMonth, 
    "WeekNumber", 
    each Date.WeekOfYear([Date], FirstDayOfWeek)
  ), 
  WeekIndex = Table.AddColumn(
    WeekNumber, 
    "WeekIndex", 
    each 
      let
        Multiply = [Year] - FirstYear, 
        Index    = 53 * Multiply + [WeekNumber]
      in
        Index
  ), 
  WeekOfMonth = Table.AddColumn(
    WeekIndex, 
    "WeekOfMonth", 
    each Date.WeekOfMonth([Date], FirstDayOfWeek)
  ), 
  Week = Table.AddColumn(
    WeekOfMonth, 
    "Week", 
    each Number.ToText([Year]) & " W" & Number.ToText([WeekNumber], "00"), 
    type text
  ), 
  StartOfWeek = Table.AddColumn(
    Week, 
    "StartOfWeek", 
    each Date.StartOfWeek([Date], FirstDayOfWeek), 
    type date
  ), 
  EndOfWeek = Table.AddColumn(StartOfWeek, "EndOfWeek", each Date.EndOfWeek([Date])), 
  DayOfYear = Table.AddColumn(
    EndOfWeek, 
    "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
  ), 
  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
  ), 
  DayOfMonth = Table.AddColumn(DayOfQuarter, "DayOfMonth", each Date.Day([Date])), 
  DayOfWeek = Table.AddColumn(DayOfMonth, "DayOfWeek", each Date.DayOfWeek([Date], Day.Monday) + 1), 
  DayName = Table.AddColumn(DayOfWeek, "DayName", each Date.ToText([Date], "dddd"), type text), 
  DayNameShort = Table.AddColumn(
    DayName, 
    "DayNameShort", 
    each Date.ToText([Date], "ddd"), 
    type text
  ), 
  PercentOfMonth = Table.AddColumn(
    DayNameShort, 
    "PercentOfMonth", 
    each [DayOfMonth] / [DaysInMonth], 
    Percentage.Type
  ), 
  Weekday = Table.AddColumn(
    PercentOfMonth, 
    "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
  ), 
  IsInPreviousMonth = Table.AddColumn(
    CurrentWeek, 
    "IsInPreviousMonth", 
    each Date.IsInPreviousMonth([Date]), 
    type logical
  ), 
  IsBeforeToday = Table.AddColumn(
    IsInPreviousMonth, 
    "IsBeforeToday", 
    each [Date] <= Date.From(DateTime.LocalNow()), 
    type logical
  ), 
  Today = Table.AddColumn(
    IsBeforeToday, 
    "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
  ), 
  YearCompleted = Table.AddColumn(
    CurrentWTD, 
    "YearCompleted", 
    each Date.EndOfYear([Date]) < Date.From(Date.EndOfYear(Date.From(DateTime.LocalNow()))), 
    type logical
  ), 
  QuarterCompleted = Table.AddColumn(
    YearCompleted, 
    "QuarterCompleted", 
    each Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(Date.From(DateTime.LocalNow()))), 
    type logical
  ), 
  MonthCompleted = Table.AddColumn(
    QuarterCompleted, 
    "MonthCompleted", 
    each Date.EndOfMonth([Date]) < Date.From(Date.EndOfMonth(Date.From(DateTime.LocalNow()))), 
    type logical
  ), 
  Age = Table.AddColumn(
    MonthCompleted, 
    "DayOffset", 
    each [Date] - Date.From(DateTime.LocalNow()), 
    type duration
  ), 
  YearOffset = Table.AddColumn(
    Age, 
    "YearOffset", 
    each Date.Year([Date]) - Date.Year(Date.From(DateTime.LocalNow()))
  ), 
  QuarterOffset = Table.AddColumn(
    YearOffset, 
    "QuarterOffset", 
    each (4 * ([Year] - Date.Year(Date.From(DateTime.LocalNow()))))
      + ([QuarterNumber] - Date.QuarterOfYear(Date.From(DateTime.LocalNow())))
  ), 
  MonthOffset = Table.AddColumn(
    QuarterOffset, 
    "MonthOffset", 
    each (12 * ([Year] - Date.Year(Date.From(DateTime.LocalNow()))))
      + ([MonthNumber] - Date.Month(Date.From(DateTime.LocalNow())))
  ), 
  DayOffset = Table.TransformColumns(MonthOffset, {{"DayOffset", Duration.Days}}), 
  ISOWeek = Table.AddColumn(
    DayOffset, 
    "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
        )
  )
in
  ISOWeek

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.

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 bare rose mig for min pæne kode, så skriv en kommentar herunder.

Leave a Reply