cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.  ## Little DAX Formula List

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

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:

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)

)

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 =

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

1 ACCEPTED SOLUTION  Community Support

Thanks for sharing!

I'd like to value your post and mark this post as a solution so more people would find it easily.

Best Regards

Maggie

6 REPLIES 6 Anonymous
Not applicable

Hi @Drewdel ,

Thanks for sharing! This is really very useful. Appreciate it!

Would it be possible to put your code in a "Insert Code" option going forward.   To be honest, I kind of always overlook the formatting bar when using forums, I don't know why; just one of those things I guess, stupid me |P Frequent Visitor

I always use this guy as example to solve my problems -->  https://community.powerbi.com/t5/Data-Stories-Gallery/DAX-Reference-Cheat-Sheet/td-p/483212

by the way, many thanks for sharing it  Community Support

Thanks for sharing!

I'd like to value your post and mark this post as a solution so more people would find it easily.

Best Regards

Maggie  O yeah, I honestly forgot about that kind of thing and that it would come up as a question. Anonymous
Not applicable

Thanks for sharing this @Drewdel !

I'm not sure how to go about creating new quick measures, but some of these would be awesome to include!

Posts like this make the community AWESOME.

~ Chris Announcements #### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features. #### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator. #### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings. Top Solution Authors
Top Kudoed Authors
Users online (5,716)