DAX: Count Active Contracts Between(StartDate; EndDate)

Active Contracts:=CALCULATE(COUNTROWS(Contracts);
    FILTER(Contracts; ('Contracts'[StartDate] <= LASTDATE('Date table'[Date])
                              && 'Contracts'[EndDate]>= FIRSTDATE('Date table'[Date]))))

Table1: DateTable (auto-generated with Power Query)... source=first date, last date becomes today
Table2: ContractTable (Contract, StartDate, EndDate)
No links/joins

Sources: ExcelFile, Count active contracts, Auto-generate DateTable

----

Difference between SUM/SUMX/CALCULATE

Mes;= SUM( Tbl[Revenue] )   //Single Column

X-Formulas iterates on a Table and evaluates the expression for each row.
Revenue := SUMX( Sales; Sales[Price]*Sales[Quantity] )
Note: The columns must all be from the same table, or use Related if there is a relationship.

Cost1 := SUMX( Dati; Dati[Cost] * Dati[Quantity] )
Cost2 := SUMX( Dati; FILTER( Dati; Dati[Quantity]>10; [Cost] * Dati[Quantity] ) )

Picture: Row context or Filter context (from PivotTable) - link

Picture. slide52

Incorrect: Mes:= CALCULATE( SUM(...); Tbl[Col1] > Tbl[Col2] )  //Two col.

Correct  : Mes:= CALCULATE( SUM(...); Tbl[Col1] > 10 )              //One col.

Correct  : Mes:= CALCULATE( SUM(...); FILTER( Tbl; Tbl[Col1] > Tbl[Col2] ))  //Two col.

Picture: slide40

Picture: slide48

--------

VALUES

Picture: slide47


----
RANKING FORMULA
--> Ranking: 1, 2, 3, ....

ALT.1: With EARLIER()
RankingOnUnitPrice =
COUNTROWS (
      FILTER( ALL('Product');
                       'Product'[UnitPrice] > EARLIER ( 'Product'[UnitPrice]
                     )
       ) + 1

ALT.2: With VAR
RankingOnUnitPrice =
VAR
      CurrentUnitPrice = 'Product'[UnitPrice]
RETURN
COUNTROWS (
             FILTER( ALL('Product');
                             'Product'[UnitPrice] > CurrentUnitPrice
                           )
                         ) + 1

Video: The original name of EARLIER() was OUTER(); it gives you access to the Outer row Context - the previous iteration.

ALL() --> Does not look at the values, instead looks at all rows.

----
DYNAMIC GROUPINGS

Picture: (source + excel-file)

ALT1: with VAR
"Variables also has the advantage of evaluating the expression once so the expressions that reference them should perform better.". Key formula SWITCH(TRUE())

NumberOrders =
VAR SumOrders =
CALCULATE ( DISTINCTCOUNT ( FactInternetSales[SalesOrderNumber] ) )
RETURN
SWITCH (
TRUE (),
SumOrders <= 4, FORMAT ( SumOrders, “General Number” ),
SumOrders >= 5 && SumOrders <= 10, “5-10”,
SumOrders > 10, “More than 10”
)

Alt2: without VAR
"If you don’t have a variable, you need to create a separate measure for CALCULATE ( DISTINCTCOUNT ( FactInternetSales[SalesOrderNumber] ) ) and use this measure so you don’t repeat the same formula in the SWITCH statement."

Slicers for value fields

-------- VAR & RETURN FORMULA

DAX as a Language  (via forum question) + about forecasts

DAX now has variable support

----
CALCULATE
CALCULATE() is the only formula that can modify/replace (an exisiting column filter) a Filter Context (video). But not other column filters. Example a table with Colour="Red", but not Size

Alt1: RedSales = CALCULATE( Product[SalesAmount]; Product[Color]="Red")
--> same amount on every row.
Alt2: RedSales = CALCULATE( Product[SalesAmount]; FILTER(Product; Product[Color]="Red"))
--> only on the red row.
Video


----
DISTICTCOUNT of a UNION
Measure = var a = UNION(Table2,Table3) 
return 
COUNTROWS(
summarize(a,Table2[Col1])
)

Resources consumed...
Time (CPU)        : Calculated Measures
Space (Memory) : Calculated Dimensions  (Columns)

Pareto
If,  ~80% comes from 1 variable
--> >90% comes from 2 variables ?

---
Getting started with DAX Studio

-----
COMBINE EXCEL FILES

Combine data from several Excel sheets