I'm not up here that much but I just wanted to share a little list of a few DAX formulas I've made and come across through the past year I've been working with Power BI. Hope some people find some of these helpfull and maybe I'll post some more later on.
Newline Character:
UNICHAR(10)
Last N Years Calculated Column:
LastNYears = YEAR(TODAY()) - table[year column]
Forecasting Line Measure:
Forecast =
/*
Forecast Calculation by CAGR (Compound Annual Growth Rate): https://community.powerbi.com/t5/Desktop/Finance-Forecast-using-DAX/td-p/318862
https://investinganswers.com/dictionary/c/compound-annual-growth-rate-cagr
CAGR = (EV / BV)^1/n - 1
EV: Investment's ending value
BV: Investment's beginning value
n: Number of periods(months, years, etc.)
*/
VAR firstyear = FIRSTNONBLANK(ALL(datedim[year column]),[total measure])
VAR lastyear = LASTNONBLANK(ALL(datedim[year column]),[total measure])
VAR cagr =
POWER(
DIVIDE(
CALCULATE([total measure],datedim[year column] = lastyear)
,CALCULATE([total measure],datedim[year column] = firstyear)
)
,1 / (lastyear - firstyear)
) - 1
RETURN
IF(
SELECTEDVALUE(datedim[year column]) > lastyear
,CALCULATE(
[total measure]
,datedim[year col] = lastyear
) * POWER((1 + cagr),SELECTEDVALUE(datedim[year column]) - lastyear)
)
Trend Line Measure:
Trend =
-- https://community.powerbi.com/t5/Desktop/DAX-to-create-a-Trend-line/td-p/398438
VAR known =
FILTER(
SELECTCOLUMNS(
ALLSELECTED(datedim)
,"known_x" ,datedim[date column]
,"known_y" ,[total measure]
)
,AND(
NOT(ISBLANK([known_x]))
,NOT(ISBLANK([known_y]))
)
)
VAR count_items = COUNTROWS(known)
VAR sum_x = SUMX(known,[known_x])
VAR sum_x2 = SUMX(known,[known_x] ^ 2)
VAR sum_y = SUMX(known,[known_y])
VAR sum_xy = SUMX(known,[known_x] * [known_y])
VAR avg_x = AVERAGEX(known,[known_x])
VAR avg_y = AVERAGEX(known,[known_y])
VAR slope =
DIVIDE(
count_items * sum_xy - sum_x * sum_y
,count_items * sum_x2 - sum_x ^ 2
)
VAR intercept = avg_y - slope * avg_x
RETURN
SUMX(
DISTINCT(datedim[date column])
,intercept + slope * datedim[date column]
)
Miles to Location Measure:
-- https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-h...
Distance =
-- FROM (My Location)
VAR lat_from = MIN(table[latitude col])
VAR lng_from = MIN(table[longitude col])
-- TO
VAR lat_to = MIN(table[latitude col])
VAR lng_to = MIN(table[longitude col])
VAR p = DIVIDE(PI(),180)
VAR a =
0.5 - COS((lat_from - lat_to) * p) / 2
+ COS(lat_to * p)
* COS(lat_from * p)
* (1 - COS((lng_from - lng_to) * p)) / 2
VAR result = 7918 * ASIN((SQRT(a)))
RETURN
result
Ordinal Suffices:
SWITCH(
TRUE()
,MOD([number column or variable],100) IN{11,12,13} ,"th"
,MOD([number column or variable],10) = 1 ,"st"
,MOD([number column or variable],10) = 2 ,"nd"
,MOD([number column or variable],10) = 3 ,"rd"
,"th"
)
Periodical Measures - Current, Previous and Period-over-Period:
These measures calculate yearly periods, from tomorrow last-year to today, with current period starting this/selected year and previous starting a year prior to this/selected year.
CurrentPeriod =
VAR current_date = LASTNONBLANK(datedim[date column],[total measure])
VAR ly_date = NEXTDAY(SAMEPERIODLASTYEAR(current_date))
VAR date_context =
DATESBETWEEN(
datedim[date column]
,NEXTDAY(SAMEPERIODLASTYEAR(current_date))
,LASTDATE(current_date)
)
VAR result = SUMX(date_context,[total measure])
RETURN
result
PreviousPeriod =
VAR current_date = LASTNONBLANK(datedim[date column],[total measure])
VAR ly_date = NEXTDAY(SAMEPERIODLASTYEAR(current_date))
VAR date_context =
DATESBETWEEN(
datedim[date column]
,NEXTDAY(SAMEPERIODLASTYEAR(current_date))
,LASTDATE(current_date)
)
VAR result = SUMX(DATEADD(date_context,-1,YEAR),[total measure])
RETURN
result
PeriodOverPeriod =
DIVIDE(
[CurrentPeriod] - [PreviousPeriod]
,[PreviousPeriod]
) * SIGN([PreviousPeriod])
Total Sum of Positives or Negatives:
TotalPositives =
SUMX(
FILTER(
table
,table[column to be summed] > 0
)
,table[column to be summed]
)
TotalNegatives =
SUMX(
FILTER(
table
,table[column to be summed] < 0
)
,table[column to be summed]
)
N Day Aggregate (SUM or AVERAGE):
CALCULATE(
Aggregate
,DATESINPERIOD(table[date column],TODAY(),n,Day)
)
12 Month Rolling Totals:
12MonthRolling =
-- https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/
-- https://www.wiseowl.co.uk/blog/s2477/moving-averages.htm
VAR date_context =
DATESBETWEEN(
datedim[date column]
,NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(datedim[date column])))
,LASTDATE(datedim[date column])
)
VAR yearly_profits =
CALCULATE(
[total measure]
,date_context
)
VAR yearly_period =
CALCULATE(
CALCULATE(
COUNTROWS(VALUES(datedim[month column]))
,table
)
,date_context
)
VAR yearly_avg = DIVIDE(yearly_profits,yearly_period)
VAR result =
DIVIDE(
[total measure]
,[total measure]
) * yearly_avg
RETURN
result
7 Day Rolling Totals:
7DayRolling =
VAR date_context =
DATESINPERIOD(
datedim[date column]
,LASTDATE(datedim[date column])
,-7
,DAY
)
VAR weekly_profits =
CALCULATE(
[total measure]
,date_context
)
VAR weekly_period =
CALCULATE(
CALCULATE(
COUNTROWS(VALUES(datedim[date column]))
,table
)
,date_context
)
VAR weekly_avg = DIVIDE(weekly_profits,weekly_period)
VAR result =
DIVIDE(
[total measure]
,[total measure]
) * weekly_avg
RETURN
result
