Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power Bi forecast line chart

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?

datePeriodToolpower consumption per period
12-Jun1A20
15-Jun1B20
05-Jul2C60
31-Jul3D5
03-Aug4E21
04-Aug4F21
 5  
 6  
 7  
Help me please
  • ValtteriN's avatar
    ValtteriN
    4 years ago

    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.

6 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi ValtteriN,

      thanks for your time. Does not work for me?

      any idea why it does not show values ?

       

       

      • ValtteriN's avatar
        ValtteriN
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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.