Forum Discussion

gonrodrigues's avatar
gonrodrigues
Helper III
10 years ago
Solved

Variation Column

Hello!

 

 

I need to create a new column in "Variação" (variation in english) table, called "Var Headcount" in which the 1st row is:

 

[nr of rows with 'domingo, 1 de Maio de 2016' associated in 'QP' table] - [nr of rows with 'sexta-feira, 1 de Abril de 2016' associated in 'QP' table]

 

2nd row:

 

[nr of rows with 'sexta-feira, 1 de Abril de 2016' associated in 'QP' table] - [nr of rows with 'terça-feira, 1 de Março de 2016' associated in 'QP' table]

 

and so on...

 

How can i do this?

 

Thanks!

Need Help
  • sornavoor's avatar
    sornavoor
    10 years ago

    Please see the below if it works for you.... 

     

    2 Steps:  

     

    Step 1 Count of related rows as a column in Date Table:

    QP Row Count = IF(COUNTROWS(RELATEDTABLE(QP))>0,COUNTROWS(RELATEDTABLE(QP)), 0)

     

    Step 2: Difference in Count from Prior Month:

     

    Difference From Prior Month = 'Date Table'[QP Row Count] - CALCULATE(SUM('Date Table'[QP Row Count]),DATEADD('Date Table'[Date],-1,MONTH))

     

     

     


    Data ModelQP Table

    Step 1 - Add a Related Row Count ColumnStep 2 - Add a Difference in Row Count from prior Month

2 Replies

  • sornavoor's avatar
    sornavoor
    Resolver I
    10 years ago

    Please see the below if it works for you.... 

     

    2 Steps:  

     

    Step 1 Count of related rows as a column in Date Table:

    QP Row Count = IF(COUNTROWS(RELATEDTABLE(QP))>0,COUNTROWS(RELATEDTABLE(QP)), 0)

     

    Step 2: Difference in Count from Prior Month:

     

    Difference From Prior Month = 'Date Table'[QP Row Count] - CALCULATE(SUM('Date Table'[QP Row Count]),DATEADD('Date Table'[Date],-1,MONTH))

     

     

     


    Data ModelQP Table

    Step 1 - Add a Related Row Count ColumnStep 2 - Add a Difference in Row Count from prior Month