Time intelligence calculations Time intelligence calculations DAX 35

Time intelligence calculations

Какво да разбираме под Time intelligence calculations В DAX разполагаме с 35+ функции за Time intelligence calculations. виж : https: //msdn. microsoft. com/en-us/library/ee 634763. aspx Например: SAMEPERIODLASTYEAR(<dates>) Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.

automatic Date table created by Power BI

Note Because the term Date is a reserved keyword in DAX (it corresponds to the function DATE) you must embed the Date name in quotes when referring to the table/column name, even if there are no spaces or special characters in that name. You might prefer using „Dates“ or „dim. Dates” instead of „Date“ as the name of the table/ table_column to avoid this requirement.

Dates = CALENDAR ( DATE ( YEAR ( MIN ( Sales[Order Date] ) ), 1, 1 ), DATE ( YEAR ( MAX ( Sales[Order Date] ) ), 12, 31 ) ) CALENDAR

FYDates = VAR Min. Year = YEAR ( MIN ( Sales[Order Date] ) ) VAR Max. Year = YEAR ( MAX ( Sales[Order Date] ) ) RETURN FILTER ( CALENDARAUTO (6), YEAR ( [Date] ) >= Min. Year && YEAR ( [Date] ) <= Max. Year ) CALENDARAUTO

„calendar table“ дефинирани чрез DAX CALENDARAUTO Function • Year = YEAR([Date]) • Day = Format([Date], "DDDD") • Day. Of. Month = DAY([Date]) • Month. Of. Year = MONTH([Date]) • Month = FORMAT([Date], "MMM") & " " & [Year] • Quarter. Of. Year = ROUNDUP (MONTH([Date])/3, 0) • Quarter = "Q" & [Quarter. Of. Year] & " " & [Year] • Ordinal. Date = DATEDIFF([Year]&", 1, 1", [Date], DAY)+1 • Day. Of. Week = WEEKDAY([Date], 2) • Week. Ending = [Date] + (7 - [Day. Of. Week])

Dates = VAR Min. Year = YEAR ( MIN ( Sales[Order Date] ) ) VAR Max. Year = YEAR ( MAX ( Sales[Order Date] ) ) RETURN ADDCOLUMNS ( FILTER ( CALENDARAUTO ( ), Код който можем да изпълним в Power. BI YEAR ( [Date] ) >= Min. Year && YEAR ( [Date] ) <= Max. Year ), "Year", YEAR ( [Date] ), "Quarter Number", INT ( FORMAT ( [Date], "q" ) ), "Quarter", "Q" & INT ( FORMAT ( [Date], "q" ) ), "Month Number", MONTH ( [Date] ), "Month", FORMAT ( [Date], "mmmm" ), "Week Day Number", WEEKDAY ( [Date] ), "Week Day", FORMAT ( [Date], "dddd" ), "Year Month Number", YEAR ( [Date] ) * 100 + MONTH ( [Date] ), "Year Month", FORMAT ( [Date], "mmmm" ) & " " & YEAR ( [Date] ), "Year Quarter Number", YEAR ( [Date] ) * 100 + INT ( FORMAT ( [Date], "q" ) ), "Year Quarter", "Q" & FORMAT ( [Date], "q" ) & "-" & YEAR ( [Date] ) )

готови шаблони за „calendar table“ https: //www. sqlbi. com/tools/dax-date-template/ Изтеглете от http: //elearn. uni-sofia. bg файла Time Intelligence Functions Start. pbix В него има ли „calendar table“? Дефинирайте „calendar table“ с помощта на DAX Какви нови релации можете да създадете? Date -> Order Date -> Delivery Date

Мерки с филтри за дати Дефинирайте мярка Sales Amount която да изчислява всички продажби Sales Amount = SUMX ( Sales, Sales[Net Price] * Sales[Quantity] ) Дефинирайте мярка Sales Amount Jan-Feb 2007 която да изчислява продажбите през месеците януари и февруари 2007 Sales Amount Jan-Feb 2007 = CALCULATE ( SUMX ( Sales, Sales[Net Price] * Sales[Quantity] ), FILTER ( ALL ( 'dim. Dates' ), AND ( 'dim. Dates'[Date] >= DATE ( 2007, 1, 1 ), 'dim. Dates'[Date] <= DATE ( 2007, 2, 28 ) )

Задача с филтри за дати Създайте мярката Sales Amount YTD която да изчислява всички продажби през текущата година до края на месеца зададен в контекста на отчета Има ли по-лесен начин? Sales Amount YTD = VAR Last. Visible. Date = MAX ( 'dim. Dates'[Date] ) VAR Current. Year = YEAR ( Last. Visible. Date ) VAR Set. Of. Dates. Ytd = FILTER ( ALL ( 'dim. Dates'[Date] ), AND ( 'dim. Dates'[Date] <= Last. Visible. Date, YEAR ( 'dim. Dates'[Date] ) = Current. Year ) ) VAR Result = CALCULATE ( SUMX ( Sales, Sales[Net Price] * Sales[Quantity] ), Set. Of. Dates. Ytd ) RETURN Result

Sales Amount YTD 2 = CALCULATE ( SUMX ( Sales, Sales[Net Price] * Sales[Quantity] ), DATESYTD ( 'dim. Dates'[Date] ) ) Sales Amount YTD 3 = CALCULATE ( [Sales Amount], DATESYTD ( 'dim. Dates'[Date] ) ) http: //www. daxpatterns. com/time-patterns/