Power Bi forecast line chart

[Anonymous]

Hi there,

I am looking for power consumption per period chart but with filter on 

values from Tool column (Tools names are never the same).

Power consumption for periods 5,6,7 should be a prediction based on the previous values.

Period 5 should be avg of values for period 4,3,2  in this case "(21 + 5 + 60) / 3"

Period 6 = avg for 4 last periods;  Period 7 = avg for 5 previous periods if exists

Can power bi do anything like that?

date	Period	Tool	power consumption per period
12-Jun	1	A	20
15-Jun	1	B	20
05-Jul	2	C	60
31-Jul	3	D	5
03-Aug	4	E	21
04-Aug	4	F	21
	5
	6
	7

[ValtteriN]

Hi,

Here is one way to do this:

Period Value =

var selection = SELECTEDVALUE('Table (2)'[Period])

var vartable = SUMMARIZE(FILTER(all('Table (2)'),

'Table (2)'[Period]>=2 && 'Table (2)'[Period]<5)

,'Table (2)'[Period],'Table (2)'[power consumption per period]) //Here I take the previous periods values into a variable table

var averagevar = AVERAGEX(vartable,'Table (2)'[power consumption per period]) //Calculate average for the previous periods (28.67)

var vartable2 = Union(

vartable,{("5",averagevar)}) //create another var table with the previous value

var averagevar2 = AVERAGEX(vartable2,'Table (2)'[power consumption per period]) //again calculate average it is the same since we have one more row and the perious value are otherwise the same

var vartable3 = union(vartable2,{("6",averagevar2)})

var averagevar3 = AVERAGEX(vartable3,'Table (2)'[power consumption per period]) //same as previous step

return

SWITCH(TRUE(),selection=5,

averagevar

,selection=6,

averagevar2

,selection=7

,averagevar3

) //select value depending on the row value

I hope this helps and if it does consider accepting this post as a solution!

[Anonymous]

Hi ValtteriN,

thanks for your time. Does not work for me?

any idea why it does not show values ?

[ValtteriN · Accepted solution]

Hi,

The Dax I created was for a measure. If you want to use it in a calculated column it needs to be a bit different. Actually in this case I recommend using a calculated table:

Period Value =

var selection = 5

var vartable = SUMMARIZE(FILTER(all('Table (2)'),

'Table (2)'[Period]>=2 && 'Table (2)'[Period]<5)

,'Table (2)'[Period],'Table (2)'[power consumption per period],'Table (2)'[Tool]) //Here I take the previous periods values into a variable table

var averagevar = AVERAGEX(vartable,'Table (2)'[power consumption per period]) //Calculate average for the previous periods (28.67)

var vartable2 = Union(

vartable,{("5",averagevar,"")}) //create another var table with the previous value

var averagevar2 = AVERAGEX(vartable2,'Table (2)'[power consumption per period]) //again calculate average it is the same since we have one more row and the perious value are otherwise the same

var vartable3 = union(vartable2,{("6",averagevar2,"")})

var averagevar3 = AVERAGEX(vartable3,'Table (2)'[power consumption per period]) //same as previous step

var vartable4 =union(vartable3,{("7",averagevar3,"")})

return

vartable4 //select value depending on the row value

However, If there isn't any specific reason to use a calculated column you can just use the measure version.

[Anonymous]

Thanks again. I did apply measurement and it shows data. I can filter periods 1,2,3,4 on that. But it does not affect our predicted 5,6,7. Is it possible to 5,6,7 are changing once previous are selected/deselected ?

on the top of it average for Period 5 should be distinct values for period 4,3,2 so it should like a (21+6 + 60)/3 =29

and period 6 (21+6 + 60+20)/4 = 26.75

Like if period 5 includes 3 previous values , period 6 includes 4 previous values, period 7 includes 5 previous values(Periods).

== about column verison - there was an error

[ValtteriN]

Ah okay, I understood that you wanted the calculation for period 6 to count previous 4 periods so that the period 5 prediction is included in the calculation. This should be closer to what your are describing:

Period Value 2 =

var selection = SELECTEDVALUE('Table (2)'[Period])

var switchSel = SWITCH(TRUE(),selection=5,2,selection=6,1,selection=7,1)

var vartable = SUMMARIZE(FILTER(all('Table (2)'),

'Table (2)'[Period]>=switchSel && 'Table (2)'[Period]<5)

,'Table (2)'[Period],'Table (2)'[power consumption per period]) //Here I take the previous periods values into a variable table

var averagevar = AVERAGEX(vartable,'Table (2)'[power consumption per period]) //Calculate average for the

return

if(selection>4,averagevar,"")

[Anonymous]

I meant that 5,6,7 are based on 4,3,2,1

5 = avg of 4,3,2 divided by 3

6= avg of 4,3,2,1 divided by 4

7 = avg of 4,3,2,1, (next one if exists) divided by 5

You did it almost perfect last time.

I just need filtered predictions on Tool.

5,6,7 values should be changeable.

like:

Period 4 ; power=21; Tools E , F

Period 3 ; power = 5; Tools D

Period 2 ; power = 60;Tools C

Period Value= Period 5 = (60 +5 +21)/3 = 28.6 

and now I would like to filter my chart...

Period 5 = filter : select F only

And period 5 should be changed to:

period 5 =  (60 +5 +10.5)/ 3 = 25.16

because 

  period 4; power consumption per period = 21;  E=10.5 and F=10.5

same for 6 and 7

date	Period	Tool	power consumption per period
12-Jun	1	A	20
15-Jun	1	B	20
05-Jul	2	C	60
31-Jul	3	D	5
03-Aug	4	E	21
04-Aug	4	F	21
	5
	6
	7