0

I have data in a PowerPivot data model with two different date fields, one for the added date, and another for a removed date. I would like to create a calculation that will allow me to summarize a net change by month. Meaning I want to aggregate the count of the adds by month, the removes by month and the net change by month.

Enclosing an example of the data (A) and ultimately what I would like to aggregate (B).

Any direction would be appreciated. Thanks!

Sample of data and desired result

enter image description here

Tomasz Jakub Rup
  • 10,502
  • 7
  • 48
  • 49
Joe
  • 23
  • 1
  • 6

1 Answers1

1

Create two relationships between your fact table and your date dimension. One will have to be inactive. I'll assume an active relationship between Fact[Added] and DimDate[Date] and an inactive relationship between Fact[Removed] and DimDate[Date].

Adds:=
COUNTROWS( 'Fact' )

Removes:=
CALCULATE(
    COUNTROWS( 'Fact' )
    ,USERELATIONSHIP( 'Fact'[Removed], DimDate[Date] )
)

Net:=
[Adds] - [Removes]

Make sure that you use the date field from your date dimension in the pivot table, and not either of the date fields in your fact table.

greggyb
  • 3,728
  • 1
  • 11
  • 32