How to Correctly Calculate the Number of Transaction Open and Closed in A Period

Question

I have Four Tables

1. a View Dimension Table
2. a Transaction Fact Table
3. a Transaction OpenDate Table
4. a Transaction ClosedDate Table

they are Related as Follows

Using this structure I have Been Able to sucessfully create a Count of Transaction Created in Any Periid and Transactions Closed on Any Period.

Transactions Open=COUNTA('Transaction Open'[TransactionNo])
Transactions Clsed=COUNTA('Transaction Closed'[TransactionNo])

What I want however is aslo to count how many Transaction were open and Closed With a Period . Because I cannot have any more Relationships I pulled the Transaction Created date into the Transaction Closed PowerPivot Table Using the following

Transaction Close Table[XcreatedDate]=MAXX(FILTER(ALL('Transaction Open Table'),'Transaction OPen Table'[TransactionNo]= 'Transaction Closed Table'[Transaction No]),'Transaction Open Table'[OpenDate])

So I tried to Create a Measure OpenClosedInPeirod on The Transaction Closed Date Table as follows

OpenClosed =COUNTROWS('Transaction Closed Date', 'Transaction Closed Date'[Xcreated]>=FIRSTDATE('VTimeDimension[Date]) && 'Transaction Closed Date'[Closeddate]<=LASTDATE('VTimeDimension[Date]))

This Worked for Days and Weeks but Fails to Aggregate correctly for Month and Quarters

Any Suggestions

Answer 1

See:

http://www.powerpivotblog.nl/powerpivot-dax-case-resouce-planning-report-with-selecting-a-date-parameter

HTH!

David Hager

Excel FMVP