Variation Column

[gonrodrigues]

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!

[sornavoor · Accepted solution]

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

[gonrodrigues]

Worked perfectly!!!

Thanks a lot @sornavoor 🙂