itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Power BI: trend line

The easier way to put a trend line is to put in the “x-axis” of the chart a number. For instance, instead to put the month name “January, February, etc.”, put “1, 2, etc” or whatever numbers then make sure to have the “continuous” (not categorical) type selected (left picture) and the “trend line” option will appear (right picture):

power bi power bi

Now if this option doesn’t appear, this is how I will create it. Let´s take an example, this is my data and my chart:

power bi power bi
power bi

First, I will create 3 columns:

  • A new index column with this formula: IF(NOT(ISBLANK('table'[value])),'table'[index])
    power bi it will allow to keep the trend line in progress based on the last data. For instance:
    power bi
  • A trend number column: it will calculate where to put each point to draw the line
  • A trend line column: it will allow to draw the increase/decrease/equal line
power bi

This is the formula for the trend number:


var xdiff=put-a-number // put a number to where to start and to end the trend line
var xstart=CALCULATE(MIN('table'[value]),FILTER(ALL('table'),'table'[new index]=MIN('table'[new index])))
var xend=CALCULATE(MAX('table'[value]),FILTER(ALL('table'),'table'[new index]=MAX('table'[new index])))
var xnbmonth=COUNT('table'[value])-1
var xilastvalue=CALCULATE(MAX('table'[value]),FILTER(ALL('table'),'table'[new index]=MAX('table'[new index])))-xdiff
var xinbunique=IF(NOT(ISBLANK(VALUE('table'[value]))),(xilastvalue-xdiff)/xnbmonth)
var xdmainvalue=CALCULATE(MIN('table'[value]),FILTER(ALL('table'),'table'[new index]=MIN('table'[new index])))-xdiff
var xdnbunique=IF(NOT(ISBLANK(VALUE('table'[value]))),(xdmainvalue-xdiff)/xnbmonth)
var xdecrease=IF('table'[new index]=1,0,xdnbunique)
var xincrease=IF(NOT(ISBLANK(CALCULATE(MIN('table'[value]),'table'[new index]=MIN('table'[new index])))),xdiff,xinbunique)
return
IF(xend<xstart,xdecrease,IF(xend>xstart,xincrease))
power bi

This is the formula for the trend line:


var xdiff=put-a-number // put a number to where to start and to end the trend line
var xstart=CALCULATE(MIN('table'[value]),FILTER(ALL('table'),'table'[new index]=MIN('table'[new index])))
var xend=CALCULATE(MAX('table'[value]),FILTER(ALL('table'),'table'[new index]=MAX('table'[new index])))
var xdmainvalue=CALCULATE(MIN('table'[value]),FILTER(ALL('table'),'table'[new index]=MIN('table'[new index])))-xdiff
var xincrease=CALCULATE(SUM('table'[trend number]),FILTER('table','table'[new index]<=EARLIER('table'[new index])))
var xdecrease=IF(NOT(ISBLANK('table'[new index])),xdmainvalue-CALCULATE(SUM('table'[trend number]),FILTER('table','table'[new index]<=EARLIER('table'[new index]))))
Return
IF(xend<xstart,xdecrease,IF(xend>xstart,xincrease,IF(xend=xstart,IF(NOT(ISBLANK('table'[new index])),xstart/2))))
power bi

NOTE:

  • Change “table”, “value”, “new index” and “trend number” by yours
  • Change “put-a-number” by yours. If I put 0, the trend line will go from the top to the bottom like that:
    power bi

Once I will add the trend line in the “line y-axis”, this is the result for the full year:

power bi power bi

Now I don’t know why but for some reports, I got a circular dependency error message for the “trend line” formula, in this case, the workaround is to export the “month” and the “new index” columns in a new table and create another trend line column:

power bi

This is the formula to create the table2:

SELECTCOLUMNS('table',"name column",[argument1],"name column",[argument2])

This is the formula to create the new trend line column:


var xdiff=put-a-number // put a number to where to start and to end the trend line
var xstart=CALCULATE(MIN('table1'[value]),FILTER(ALL('table1'),'table1'[new index]=MIN('table1'[new index])))
var xend=CALCULATE(MAX('table1'[value]),FILTER(ALL('table1'),'table1'[new index]=MAX('table1'[new index])))
var xdmainvalue=CALCULATE(MIN('table1'[value]),FILTER(ALL('table1'),'table1'[new index]=MIN('table1'[new index])))-xdiff
var xincrease=CALCULATE(SUM('table1'[trend number]),FILTER('table1','table1'[new index]<=EARLIER('table2'[new index])))
var xdecrease=IF(NOT(ISBLANK('table2'[new index])),xdmainvalue-CALCULATE(SUM('table1'[trend number]),FILTER('table1','table1'[new index]<=EARLIER('table2'[new index]))))
Return
IF(xend<xstart,xdecrease,IF(xend>xstart,xincrease,IF(xend=xstart,IF(NOT(ISBLANK('table2'[new index])),xstart/2))))
power bi

Once done, I can delete the column with the circular dependency error and use this new trend line for my chart but first, I will have to create a relationship like that:

power bi

Interesting Topics