cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

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


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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Little DAX Formula List

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

View solution in original post

6 REPLIES 6
Highlighted
Anonymous
Not applicable

Re: Little DAX Formula List

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

Highlighted
Community Support
Community Support

Re: Little DAX Formula List

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

View solution in original post

Highlighted
Advocate I
Advocate I

Re: Little DAX Formula List

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

Highlighted
Frequent Visitor

Re: Little DAX Formula List

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

Highlighted
Super User I
Super User I

Re: Little DAX Formula List

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.

This makes more readable .

 

Capture55.PNG

Highlighted
Advocate I
Advocate I

Re: Little DAX Formula List

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors