cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Drewdel Frequent Visitor
Frequent Visitor

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
Community Support Team
Community Support Team

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

6 REPLIES 6
ChrisHaas Established Member
Established Member

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

Community Support Team
Community Support Team

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

Drewdel Frequent Visitor
Frequent Visitor

Re: Little DAX Formula List

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

gofgabriel Frequent Visitor
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

tejaswidmello Established Member
Established Member

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

Drewdel Frequent Visitor
Frequent Visitor

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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 6 members 2,589 guests
Please welcome our newest community members: