Laiko intelektas DAX: YTD, praėję metai ir laikotarpių palyginimai

Laiko intelektas DAX kalboje yra funkcijų grupė, kuri Power BI modelyje skaičiuoja rodiklius pagal laikotarpius. Su ja gaunate sumą nuo metų pradžios, to paties laikotarpio praėjusių metų rezultatą, praėjusio mėnesio palyginimą arba paskutinių trijų mėnesių vidurkį. Kad šios funkcijos veiktų, modelyje reikia teisingai sukurtos ir pažymėtos kalendoriaus lentelės. Toliau rasite, kaip ją sukurti, kurias funkcijas naudoti ir kaip pritaikyti jas pajamų, sąnaudų ir pelno analizei.

Trumpai

• Laiko intelekto funkcijos leidžia skaičiuoti ir lyginti rodiklius pagal dienas, mėnesius, ketvirčius ir metus.
• Be atskiros kalendoriaus lentelės, kurioje yra visos datos be spragų ir kuri susieta su faktų lentele, funkcijos grąžina tuščias arba klaidingas reikšmes.
• TOTALYTD skaičiuoja sumą nuo metų pradžios, SAMEPERIODLASTYEAR perkelia laikotarpį lygiai vieneriais metais atgal, DATEADD perkelia bet kokiu intervalu.
• Microsoft dabar rekomenduoja kalendoriumi pagrįstą laiko intelektą: jis lankstesnis ir veikia su finansiniais bei prekybos kalendoriais.
• Rivilė ir Finvalda naudotojams paruošti Power BI rinkiniai leidžia matyti pajamas, sąnaudas ir pelną be rankinio ataskaitų atnaujinimo.

Turinys

• Kas yra laiko intelektas ir kam jis reikalingas
• Kalendoriaus lentelė: be jos funkcijos neveiks
• TOTALYTD, SAMEPERIODLASTYEAR ir DATEADD: kuo jos skiriasi
• Matai žingsnis po žingsnio
• Pajamos, sąnaudos ir pelnas pagal laikotarpius
• Dažniausios klaidos ir kaip jas rasti
• Klasikinis ar kalendoriumi pagrįstas laiko intelektas
• Mūsų požiūris
• Paruošti Power BI rinkiniai Rivilė ir Finvalda naudotojams
• DUK
• Šaltiniai

Kas yra laiko intelektas ir kam jis reikalingas

DAX (Data Analysis Expressions) yra formulių kalba, kuria Power BI modelyje kuriami matai ir skaičiavimai. Paprasta suma atsako į klausimą „kiek pardavėme?“. Laiko intelektas atsako į vadovui svarbesnius klausimus: kiek pardavėme nuo metų pradžios, ar šis mėnuo geresnis už praėjusį ir kaip atrodome palyginti su tuo pačiu laikotarpiu pernai.

Pagal Microsoft dokumentaciją, DAX laiko intelekto funkcijos leidžia valdyti duomenis pagal dienas, mėnesius, ketvirčius ir metus, o tada kurti ir lyginti skaičiavimus tarp šių laikotarpių. Microsoft mokymų modulyje pabrėžiama, kad šios funkcijos skirtos tendencijų, augimo ir rezultatų analizei laike.

Praktikoje laiko intelektas dažniausiai naudojamas:

1. KPI kortelėse: pajamos nuo metų pradžios, pelno marža per mėnesį.
2. Tendencijų grafikuose: einamieji metai ir praėję metai vienoje ašyje.
3. Mėnesio palyginimuose: šis mėnuo, praėjęs mėnuo, pokytis procentais.
4. Vadovų ataskaitose: ketvirčio rezultatai, slenkantys 3 ar 12 mėnesių vidurkiai.

Kalendoriaus lentelė: be jos funkcijos neveiks

Dažniausia priežastis, kodėl laiko intelektas „neveikia“, yra ne formulė, o modelis. Microsoft datų lentelių projektavimo gairėse nurodo aiškius reikalavimus:

1. Datos stulpelis turi būti datos (arba datos ir laiko) tipo.
2. Reikšmės turi būti unikalios, be tuščių (BLANK) reikšmių.
3. Negali trūkti nė vienos datos, net savaitgalių ir švenčių dienų, kai pardavimų nebuvo.
4. Lentelė turi apimti pilnus metus: nuo sausio 1 d. iki gruodžio 31 d. arba visus finansinius metus.
5. Naudojant klasikinį laiko intelektą, lentelė turi būti pažymėta kaip datų lentelė (Mark as Date Table).

Svarbu ir tai, kad faktų lentelės datos stulpelis, pavyzdžiui, sąskaitos faktūros data iš Rivilės ar Finvaldos, kalendoriumi naudoti netinka. Jame trūksta dienų, kai operacijų nebuvo, todėl laikotarpių poslinkiai tampa netikslūs. Kuriama atskira lentelė, o faktų lentelės datos su ja susiejamos ryšiu.

Paprasčiausia kalendoriaus lentelė DAX kalba:

Kalendorius =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Metai", YEAR ( [Date] ),
    "Mėnuo", MONTH ( [Date] ),
    "Ketvirtis", "K" & QUARTER ( [Date] ),
    "Metai-mėnuo", FORMAT ( [Date], "YYYY-MM" )
)

Sukūrę lentelę, pažymėkite ją kaip datų lentelę, nurodykite datos stulpelį ir sukurkite ryšį „vienas su daugeliu“ tarp kalendoriaus ir pardavimų ar didžiosios knygos lentelės. Jei duomenys ateina iš kelių šaltinių, pavyzdžiui, apskaitos programos ir Excel failų, verta pirmiausia susitvarkyti jų sujungimą: apie tai plačiau rašome straipsnyje Rivilė, Excel ir Power BI duomenų sujungimas.

TOTALYTD, SAMEPERIODLASTYEAR ir DATEADD: kuo jos skiriasi

Šios trys funkcijos sudaro daugumos finansinių ataskaitų pagrindą. Jų aprašymai pateikti Microsoft DAX funkcijų sąraše.

Funkcija	Ką grąžina	Kada naudoti
TOTALYTD	Išraiškos reikšmę nuo metų pradžios iki einamosios datos kontekste	Pajamos, sąnaudos ar pelnas nuo metų pradžios
SAMEPERIODLASTYEAR	Datų lentelę, perkeltą lygiai vieneriais metais atgal	Palyginimas su tuo pačiu laikotarpiu pernai
DATEADD	Datų lentelę, perkeltą pasirinktu intervalų skaičiumi pirmyn ar atgal	Praėjęs mėnuo, praėjęs ketvirtis, prieš dvejus metus
DATESYTD	Datų nuo metų pradžios lentelę	Kai YTD logiką norite naudoti CALCULATE viduje su papildomais filtrais
DATESINPERIOD	Datų intervalą nuo nurodytos datos	Slenkantys 3 ar 12 mėnesių rodikliai

Paprasta taisyklė: jei reikia tik „pernai tuo pačiu metu“, SAMEPERIODLASTYEAR yra trumpesnis ir aiškesnis. Jei reikia lankstumo, rinkitės DATEADD.

Matai žingsnis po žingsnio

Žemiau pateikti matai tinka pardavimų lentelei, kurioje yra sumos stulpelis ir data, susieta su kalendoriumi.

1. Bazinis matas. Visada pradėkite nuo paprasto mato, kurį vėliau naudosite kituose skaičiavimuose:

Pajamos = SUM ( Pardavimai[Suma] )

2. Pajamos nuo metų pradžios. Microsoft PL-300 praktiniame užsiėmime TOTALYTD pavyzdys rodo, kad trečiasis, neprivalomas argumentas nurodo paskutinę metų dieną, pavyzdžiui, “6-30”, kai finansiniai metai baigiasi birželio 30 d. Jei jūsų finansiniai metai sutampa su kalendoriniais, argumento nereikia:

Pajamos YTD = TOTALYTD ( [Pajamos], 'Kalendorius'[Date] )

3. Tas pats laikotarpis pernai:

Pajamos PM = CALCULATE ( [Pajamos], SAMEPERIODLASTYEAR ( 'Kalendorius'[Date] ) )

4. Pokytis procentais. DIVIDE apsaugo nuo dalybos iš nulio, kai praėjusių metų duomenų nėra:

Pajamų pokytis % =
VAR Dabar = [Pajamos]
VAR Pernai = [Pajamos PM]
RETURN DIVIDE ( Dabar - Pernai, Pernai )

5. Praėjęs mėnuo ir slenkantys 3 mėnesiai:

Pajamos praėjusį mėn. =
CALCULATE ( [Pajamos], DATEADD ( 'Kalendorius'[Date], -1, MONTH ) )

Pajamos 3 mėn. =
CALCULATE (
    [Pajamos],
    DATESINPERIOD ( 'Kalendorius'[Date], MAX ( 'Kalendorius'[Date] ), -3, MONTH )
)

Taip sukurti matai veikia bet kuriame vizualizacijos lygyje: jei grafike pasirinksite ketvirčius, SAMEPERIODLASTYEAR automatiškai grąžins praėjusių metų ketvirtį.

Pajamos, sąnaudos ir pelnas pagal laikotarpius

Tas pats principas tinka ne tik pardavimams. Finansų komandai dažniausiai reikia trijų rodiklių grupių:

• Pajamos: mėnesio, ketvirčio ir YTD sumos, palyginimas su praėjusiais metais.
• Sąnaudos: YTD sąnaudos pagal sąskaitų grupes, mėnesio pokytis, slenkantis vidurkis, kuris išlygina vienkartines išlaidas.
• Pelnas: skirtumas tarp pajamų ir sąnaudų matų, YTD pelnas ir marža.

Praktinis patarimas: pelno matą kurkite iš jau sukurtų matų, pavyzdžiui, Pelnas YTD = [Pajamos YTD] - [Sąnaudos YTD]. Taip visi skaičiai ataskaitoje remiasi ta pačia logika ir sutampa tarpusavyje.

Laiko intelektas taip pat yra pagrindas kitoms analizėms. Plano ir fakto palyginimui jis naudojamas biudžeto palyginimo ataskaitoje, ateities tendencijoms vertinti pardavimų prognozavime, o likvidumui stebėti pinigų srautų analizėje. Jei dirbate su Finvalda, pelno rodiklių struktūrą padės suprasti straipsnis apie Finvalda pelno ataskaitą.

Dažniausios klaidos ir kaip jas rasti

Kai matas grąžina tuščią reikšmę arba skaičius neatitinka apskaitos programos, tikrinkite šia tvarka:

1. Lentelė nepažymėta kaip datų lentelė. Klasikinės funkcijos, pavyzdžiui, SAMEPERIODLASTYEAR, tada dažnai grąžina BLANK.
2. Nėra ryšio tarp kalendoriaus ir faktų lentelės arba ryšys sukurtas su netinkamu stulpeliu.
3. Data saugoma kaip tekstas ar skaičius. Patikrinkite stulpelio duomenų tipą Power Query ir modelio rodinyje.
4. Datoje yra laiko dalis. Jei faktų lentelėje data turi valandas ir minutes, ryšys su dienos lygio kalendoriumi neveiks tiksliai. Microsoft reikalauja dienos lygio granuliarumo be dienos dalių.
5. Kalendoriuje trūksta datų arba metai nepilni. Tada YTD ir praėjusių metų sumos nukerpamos.
6. Ateities datos rodo sumas. Kalendorius apima visus metus, todėl grafikuose gali atsirasti „tušti“ ateities mėnesiai. Juos paslėpkite filtru arba papildomu stulpeliu, žyminčiu laikotarpius iki paskutinės duomenų datos.

Greičiausias diagnostikos būdas: į lentelės vizualizaciją sudėkite kalendoriaus mėnesį, bazinį matą ir laiko intelekto matą. Jei bazinis matas rodo skaičius, o laiko intelekto matas tuščias, problema beveik visada yra ryšyje arba datų lentelės nustatymuose.

Klasikinis ar kalendoriumi pagrįstas laiko intelektas

Power BI dabar siūlo du būdus. Microsoft laiko skaičiavimų apžvalgoje jie palyginti taip:

Kriterijus	Klasikinis	Kalendoriumi pagrįstas
Paruošimas	Vidutinis	Didesnis
Lankstumas	Mažas	Didelis
Kalendoriai	Grigaliaus arba paslinktas Grigaliaus	Bet koks: finansiniai, prekybos (445, 454, 544), 13 mėnesių, mėnulio
Savaitės skaičiavimai	Ribotai	Palaikomi
Našumas	Kai kuriais atvejais lėtesnis	Geriausias

Kalendoriumi pagrįstą laiko intelektą Microsoft pristatė kaip peržiūros (preview) funkciją ir rekomenduoja jį dėl našumo bei lankstumo. Čia kalendoriaus lentelės stulpeliai susiejami su metų, ketvirčių, mėnesių ar savaičių lygiais kalendoriaus apibrėžime. Tai ypač naudinga restoranams ir prekybai, kur rezultatai lyginami savaitėmis, arba įmonėms, kurių finansiniai metai nesutampa su kalendoriniais. Kadangi funkcija dar tobulinama, prieš diegdami ją gamybinėse ataskaitose patikrinkite jos būseną Microsoft dokumentacijoje.

Mūsų požiūris

Laiko intelektas yra viena svarbiausių Power BI galimybių finansų komandai, tačiau jo vertė atsiskleidžia tik tada, kai po juo yra tvarkingas duomenų modelis. Rivilės ar Finvaldos duomenys turi būti teisingai sujungti su kalendoriumi, sąskaitų grupės suskirstytos, o ataskaitos atnaujinamos be rankinio darbo.

Todėl Rivilė ir Finvalda naudotojams kuriame iš anksto paruoštus Power BI ataskaitų rinkinius. Juose pajamos, sąnaudos, pelnas, balansas, pardavimai, sandėlis ir skolos matomi viename modelyje, o duomenys atsinaujina automatiškai. Vadovas ar buhalteris gauna atsakymus į klausimus apie laikotarpius, užuot kiekvieną mėnesį iš naujo dėliojęs Excel lenteles.

Kai įmonė turi savitą finansinių metų struktūrą, papildomus šaltinius, tokius kaip SharePoint, CRM ar logistikos sistemos, arba šakos specifiką, sprendimą pritaikome pagal jos procesus ir esamas IT sistemas.

Analitika360

Paruošti Power BI rinkiniai Rivilė ir Finvalda naudotojams

Jei norite laikotarpių analizės be kalendoriaus lentelių kūrimo ir DAX matų derinimo, Analitika360 siūlo paruoštus ataskaitų paketus su automatiniu duomenų atnaujinimu.

• Rivilė Basic ir Finvalda Basic: 59 € per mėnesį.
• Rivilė PRO ir Finvalda PRO: 89 € per mėnesį.
• Individualūs projektai: 70 € per valandą, kai reikia pritaikytų skaičiavimų ar papildomų duomenų šaltinių.

Visus planus ir sąlygas rasite verslo analitikos kainų puslapyje, o kaip veikia rinkiniai, aprašyta puslapyje ataskaitos be rankinio darbo. Duomenų atnaujinimui iš vietinių serverių dažnai naudojamas Power BI duomenų šliuzas.

DUK

Ar laiko intelektas veikia be kalendoriaus lentelės?

Power BI turi automatinę datos ir laiko funkciją, tačiau finansinėms ataskaitoms rekomenduojama atskira kalendoriaus lentelė. Tik su ja galite valdyti finansinius metus, ketvirčius ir vienodą laikotarpių logiką visuose matuose. Klasikiniam laiko intelektui ji turi būti pažymėta kaip datų lentelė.

Kuo TOTALYTD skiriasi nuo DATESYTD?

TOTALYTD iš karto grąžina reikšmę nuo metų pradžios. DATESYTD grąžina datų lentelę, kurią naudojate CALCULATE viduje. Rezultatas paprastai toks pats, tačiau DATESYTD patogesnis, kai reikia pridėti papildomų filtrų.

Kaip skaičiuoti YTD, jei finansiniai metai nesutampa su kalendoriniais?

TOTALYTD funkcijoje nurodykite trečiąjį argumentą su paskutine finansinių metų diena, pavyzdžiui, “6-30”. Kalendoriaus lentelė turi apimti pilnus finansinius metus. Sudėtingesniems kalendoriams tinka kalendoriumi pagrįstas laiko intelektas.

Kodėl SAMEPERIODLASTYEAR grąžina tuščią reikšmę?

Dažniausiai trūksta ryšio tarp kalendoriaus ir faktų lentelės, lentelė nepažymėta kaip datų lentelė arba duomenų už praėjusius metus modelyje tiesiog nėra. Patikrinkite ir datos stulpelio tipą: tekstinė data ryšio neužtikrina.

Šaltiniai

Time intelligence functions (DAX), Microsoft Learn Use DAX time intelligence functions in Power BI semantic models, Microsoft Learn Design guidance for date tables in Power BI Desktop, Microsoft Learn Use DAX time intelligence functions in Power BI, PL-300 lab Implement time-based calculations in Power BI, Microsoft Learn Calendar-based time intelligence (Preview), Power BI Blog

Rekomendacijos

• Sandėlio apyvartumas Power BI Lietuvos įmonėms: nuo DAX iki sprendimų
• Pardavimų prognozavimas su Power BI: praktinis vadovas
• Power BI biudžeto palyginimas: kaip pastatyti patikimą ataskaitą
• ABC analizė Power BI: nuo teorijos iki veikiančio DAX modelio