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

  • 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

    Friday, April 6, 2012 8:14 PM