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.

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å 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.

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.



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: 143

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.

Leave a Reply