Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

backlog pivot

Hello All,   I have a pivot table, i need to add the backlog in the calculation: example: The first value before October-2019 need to be  6134 after that i add and decrease the other data. ...
Help me please
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous - I'm not at all clear on this. Can you post that as sample data in text in a table. And when you say add and decrease other data, what data? Can you show expected output from this data?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    i'm sorry i'll try to explain better, with your formula:

     

    TEST=6314 + CALCULATE([ENTRATE],filter('Date Table','Date Table'[Date] <=maxx('Date Table','Date Table'[Date]))) -CALCULATE([CHIUSE],filter('Date Table','Date Table'[Date] <=maxx('Date Table','Date Table'[Date])))

     

    I have the rsult below. the wrong point is because the TEST of november for example need to be (6314+9690+8806-9614-8806)=6464 not 6388 

     

    i need for all the month to add the incoming units since the first day to the max filter date (es.dic-19 =ott-19+nov-19+dic-19) and decrease the outcoming units ((es.dic-19 =ott-19+nov-19+dic-19))

     

    now the formula sum 6314 to only the incoming of the month. i hope now is more cleat my question. thank you very much.

     

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Anonymous , can you give me table format with the output column. I will create a pbix and try

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hello the table in use is:

        SEGNALAZIONI:

        Second table:

         

        the join between the 2 table is 'Data Table'[date] --> 'Segnalazioni'[Data_apertura]

         

        The measure in use for the Pivot are:

        'Data Table'[Month-Year] 

        ENTRATE = CALCULATE([Conteggio di Segnalizioni], MONTH(Segnalazioni[data_apertura]), YEAR(Segnalazioni[data_apertura]))
        CHIUSE = CALCULATE([Conteggio di Segnalizioni], MONTH(Segnalazioni[data_chiusura]), YEAR(Segnalazioni[data_chiusura]))

        RESULT:NEED TO BE backlog=6314 + SUM OF ENTRATE SINCE THE MONTH F THE ROW OF THE PIVOT -SUM OF CHIUSE SINCE THE MONTH F THE ROW OF THE PIVOT As done in the table in the previous month.

         

        the KPI need to calculate the stock of units x month.

        now with your formula i have the sum only 6314+ entate month of row - chiuse month of row 

         

        unfortunatelly the connection is dinamic so i can not share with you the pbix or the table.

        i hope is more clear. thank you Massimiliano