Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

ABOVE functions, BEFORE .........

I need to create a report that shows the growth (%) of a value to another, for example, on line 1 I have the year 2010 values and in line 2 I have the values of the year 2011, I need to create a calculation that make the evolution of each of these values, the image below is an example that produced excel in other applications like QlikView I used the functions of ABOVE, BEFORE, LEFT and rigth to make this calculation I need, I tried to do something in POWER BI however without success.

 

 

Need Help

3 Replies

  • Sean's avatar
    Sean
    Community Champion
    10 years ago

    Anonymous Do you have a Calendar Table? If you don't - check out this link provided by Matt

    http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

    You will need a calendar table

     

    TOTAL Revenue Expected = SUM(Table1[Revenue Expected Home])

PY TOTAL Revenue Expected = CALCULATE( [TOTAL Revenue Expected], PREVIOUSYEAR('Calendar'[Date]) )

YoY $ = [TOTAL Revenue Expected]-[PY TOTAL Revenue Expected]

YoY % = DiVIDE ( [YoY $], [PY TOTAL Revenue Expected], 0)
    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      Thank Sean, but from what I understood I would have to create a table with my values to be able to do what it takes, if it would give a lot of work because my database is updated 3 times a day and therefore believe I would have atualzar this table as well.

      There is a custom graphic that I downloaded from the site power bi, does this chart does not consiguiria do what it takes

       

      • Sean's avatar
        Sean
        Community Champion
        10 years ago

        Anonymous you say you have a database - do you then have a Calendar Table included? (Date Dimension)

         

        a Calendar Table is necessary for the DAX time intelligence functions to work

         

        if you don't - go to the link I included in the previous post - Matt's instructions are very easy to follow!

         

        Matt has even provided the Code - meaning you can just Copy & Paste it - and that is it

         

        and then you can use the DAX time intelligence functions

         

        I have not use this Custom Visual