-
Notifications
You must be signed in to change notification settings - Fork 0
/
Lab02 DAX.txt
99 lines (71 loc) · 2.77 KB
/
Lab02 DAX.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
CustomerType =
VAR LastOrderDateKey =
CALCULATE (
MAX ( FactInternetSales[OrderDateKey] );
USERELATIONSHIP ( DimCustomer[CustomerKey]; FactInternetSales[CustomerKey] )
)
VAR LastOrderDate =
DATE ( LEFT ( LastOrderDateKey; 4 ); MID ( LastOrderDateKey; 5; 2 ); RIGHT ( LastOrderDateKey; 2 ) )
RETURN
IF (
LastOrderDate = DimCustomer[DateFirstPurchase];
"One-time Customer";
"Return Customer"
)
---------------------------------------------------------
CustomerAge = FLOOR ( ( TODAY () - DimCustomer[BirthDate] ) / 365; 1 )
---------------------------------------------------------
CustomerAgeGroup =
SWITCH (
TRUE ();
[CustomerAge] >= 65; "65 and over";
[CustomerAge] >= 50; "50 to 64";
[CustomerAge] >= 40; "40 to 49";
[CustomerAge] >= 30; "30 to 39";
[CustomerAge] >= 18; "18 to 29";
[CustomerAge] < 18; "Under 18"
)
---------------------------------------------------------
Sales Revenue = SUM(FactInternetSales[TotalSales])
---------------------------------------------------------
Units Sold = SUM(FactInternetSales[OrderQuantity])
---------------------------------------------------------
Product Cost = SUM(FactInternetSales[TotalProductCost])
---------------------------------------------------------
Profit = SUMX(FactInternetSales; FactInternetSales[TotalSales]-FactInternetSales[TotalProductCost])
---------------------------------------------------------
Customer Count = COUNTROWS(DimCustomer)
---------------------------------------------------------
MonthYear = DimDate[EnglishMonthName] & " " & DimDate[CalendarYear]
---------------------------------------------------------
MonthYearNumber =
CONCATENATE (
DimDate[CalendarYear];
IF (
DimDate[MonthNumberOfYear] < 10;
CONCATENATE ( 0; DimDate[MonthNumberOfYear] );
DimDate[MonthNumberOfYear]
)
)
---------------------------------------------------------
QuarterYear =
CONCATENATE (
CONCATENATE ( "Q"; DimDate[CalendarQuarter] );
CONCATENATE ( " "; DimDate[CalendarYear] )
)
---------------------------------------------------------
QuarterYearNumber =
CONCATENATE ( DimDate[CalendarYear]; DimDate[CalendarQuarter] )
---------------------------------------------------------
Sales Revenue QTD = TOTALQTD([Sales Revenue];DimDate[FullDateAlternateKey])
---------------------------------------------------------
Sales Revenue YTD = TOTALYTD([Sales Revenue];DimDate[FullDateAlternateKey])
---------------------------------------------------------
Sales Revenue RT =
CALCULATE(
[Sales Revenue];
FILTER(
ALL(DimDate);
DimDate[FullDateAlternateKey] <= MAX(DimDate[FullDateAlternateKey])
)
)