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
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