-- DAX Calculation Examples

--Calculated columns

--Sales After Tax
=FactInternetSales[SalesAmount] - FactInternetSales[TaxAmt]

--Quarter and Year
="Q" & DimDate[CalendarQuarter] & " " & DimDate[CalendarYear]

--English Date
=FORMAT(DimDate[FullDateAlternateKey], "dd mmmm yyyy")

--Return Calendar Year
=RELATED(DimDate[CalendarYear])

--Daily Sales
=SUMX(RELATEDTABLE(FactInternetSales), FactInternetSales[SalesAmount])

--Classify Days by Sales Total
=IF(DimDate[DailySalesTotal]>50000
	, "High Sales Day"
	, "Low Sales Day")

--Calculated measures

--Sum of Sales Amount
=SUM('FactInternetSales'[SalesAmount])

--Average
=AVERAGE('FactInternetSales'[SalesAmount])

--Errors
--"The value for column 'SalesAmount' in table 'FactInternetSales' cannot be determined in the current context."
=FactInternetSales[SalesAmount] - FactInternetSales[TaxAmt]

--Works
--Returns same result as Sales After Tax calculated column
=  SUM(FactInternetSales[SalesAmount]) 
	- 
	SUM(FactInternetSales[TaxAmt])

--SUMX Demo
=SUMX(Consultants, Consultants[Measure1] * Consultants[Measure2])

--Calculate with boolean filter
=CALCULATE(COUNTROWS(), Consultants[Skill]="MDX")

--Calculate with table filter
=CALCULATE(COUNTROWS(), FILTER(Consultants, Consultants[Measure1]>3))

--Ratio to Total
=SUM('Consultants'[Measure1])/
	CALCULATE(SUM('Consultants'[Measure1]), ALL(Consultants))

--Ratio to All Consultants
=SUM(Consultants[Measure1])/
	CALCULATE(SUM('Consultants'[Measure1]), ALL(Consultants[Consultant]))

--Distinct Count of Skills
=COUNTROWS(VALUES(Consultants[Skill]))

--Previous Year Sales
=CALCULATE(SUM(FactInternetSales[SalesAmount])
	, DATEADD(DimDate[FullDateAlternateKey], -1, YEAR)
	, ALL(DimDate))

--Previous Year Growth
=IF(FactInternetSales[Previous Year Sales]=0
	, BLANK()
	,(SUM(FactInternetSales[SalesAmount]) 
		- 
		FactInternetSales[Previous Year Sales]) 
		/ 
		FactInternetSales[Previous Year Sales])

--YTD
=TOTALYTD(
	SUM(FactInternetSales[SalesAmount])
	, DimDate[FullDateAlternateKey]
	, ALL(DimDate))

--TTD
=CALCULATE(
	SUM(FactInternetSales[SalesAmount])
	, DATESBETWEEN('DimDate'[FullDateAlternateKey]
		, BLANK()
		,LASTDATE( 'DimDate'[FullDateAlternateKey]))
		, All('DimDate'))

--Basic rank
=COUNTROWS(
	FILTER(ALL(DimDate[DateKey])
	,(DimDate[DailySales](VALUES(DimDate[DateKey]))
	<
	DimDate[DailySales]))) + 1

--Rank only for dates 
=IF(
	COUNTROWS(VALUES(DimDate[DateKey]))>1
	, BLANK()
	,COUNTROWS(
		FILTER(ALL(DimDate[DateKey])
			,(DimDate[DailySales](VALUES(DimDate[DateKey]))
			<DimDate[DailySales]) )) + 1)
			
--Measure Selection
=IF(
	COUNTROWS(VALUES('Measure Selection'[Measure Selection]))=1
	,IF(VALUES('Measure Selection'[Measure Selection])="Sales"
		, SUM(FactInternetSales[SalesAmount])
	, IF(VALUES('Measure Selection'[Measure Selection])="Tax"
		, SUM(FactInternetSales[TaxAmt])
	, IF(VALUES('Measure Selection'[Measure Selection]) = "Freight"
		, SUM(FactInternetSales[Freight])
		, BLANK()))))
		
--Calculation Selection
=IF(
	COUNTROWS(VALUES('Calculation Selection'[Calculation Selection]))=1
	, IF(VALUES('Calculation Selection'[Calculation Selection])="Actual Value"
	, 'Measure Selection'[Measure Value]
	, IF(VALUES('Calculation Selection'[Calculation Selection]) = "Previous Year"
	, CALCULATE('Measure Selection'[Measure Value]
		, DATEADD(DimDate[FullDateAlternateKey], -1, YEAR)
		, ALL(DimDate))
	, IF(VALUES('Calculation Selection'[Calculation Selection])="YTD"
		, TOTALYTD('Measure Selection'[Measure Value]
			,DimDate[FullDateAlternateKey]
			, ALL(DimDate))
	, BLANK()
	))))