Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calulating help

Hello ! How do you calculate minus in power bi, i.e. I have a baseline = 208 and then I have another field with values ​​to be accumulated downwards, see my example:   Forecast is calculated from ...
  • some_bih's avatar
    some_bih
    2 years ago

    Hi Anonymous for solution as below, please create Date  / Calendar Table in Power BI and connect / create relationship with your Year column. 

    After that please create following measures 83), adopt Sheet1 with your table name

    To get visuals as below, please use Year colum from created Date / Calendar table

    Sum of Base = SUM(Sheet1[Baseline])
    Sum of Effekt CO2 = sum(Sheet1[Effekt CO2])
     
    Running total =
    VAR __max_date = MAX ( 'Date'[Date] )
    VAR __base_line=[Sum of Base]
    VAR __running_total=
        CALCULATE (
            [Sum of Effekt CO2],          
            'Date'[Date] <= __max_date,  
            ALL ( 'Date' )              
        )
    VAR __Result=__running_total+__base_line
    RETURN __Result

    Did I answer your question? Kudos appreciated / accept solution!

     

     

     

some_bih's avatar
some_bih
Community Champion
2 years ago

Hi Anonymous "it depends 🙂", provide more model data, relationships and for variable to get some feedback as possible solution.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hello again! That is, I want the calculations to start at 208, but then it should be accumulated based on this table:

 

 

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    Hi Anonymous Inforande is Date type column? In column Basline there will be always single amount for each year, like 208?

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous Informande is Date type column? It is only a date column with Year


      In column Baseline there will always be a single amount for each year, like 208? Yes only 208, but I don't know if I'm thinking wrong or if the year 2022 should start with 208 and then calculate it based on "Summa of Effect CO2 (sustainability)"

      • some_bih's avatar
        some_bih
        Community Champion
        2 years ago

        Hi Anonymous for solution as below, please create Date  / Calendar Table in Power BI and connect / create relationship with your Year column. 

        After that please create following measures 83), adopt Sheet1 with your table name

        To get visuals as below, please use Year colum from created Date / Calendar table

        Sum of Base = SUM(Sheet1[Baseline])
        Sum of Effekt CO2 = sum(Sheet1[Effekt CO2])
         
        Running total =
        VAR __max_date = MAX ( 'Date'[Date] )
        VAR __base_line=[Sum of Base]
        VAR __running_total=
            CALCULATE (
                [Sum of Effekt CO2],          
                'Date'[Date] <= __max_date,  
                ALL ( 'Date' )              
            )
        VAR __Result=__running_total+__base_line
        RETURN __Result

        Did I answer your question? Kudos appreciated / accept solution!