Forum Discussion

pcuezze's avatar
pcuezze
Frequent Visitor
7 years ago
Solved

Using invoice # to create an invoice date column

I have a single table that contains both charges and payments tied to an Invoice #. The date field shows the date of the ledger transaction (be it charge or payment)  I would like to create a calculated column that populates each record with the date of the charge.  See below for what I'm trying to accomplish.  Thank you, everyone, again, in advance, for your assistance.

 

 

 

Need Help
  • TeigeGao's avatar
    TeigeGao
    7 years ago

    Hi pcuezze ,

    We can use the following DAX query:

    Date of Service =
CALCULATE (
    MIN ( 'Transaction Table'[Date of Trx] ),
    FILTER (
        ALL ( 'Transaction Table' ),
        'Transaction Table'[Invoice#] = EARLIER ( 'Transaction Table'[Invoice#] )
            && 'Transaction Table'[Trx Type] = "Charge"
    )
)

    The result will like below:

    Best Regards,

    Teige

1 Reply

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    Hi pcuezze ,

    We can use the following DAX query:

    Date of Service =
CALCULATE (
    MIN ( 'Transaction Table'[Date of Trx] ),
    FILTER (
        ALL ( 'Transaction Table' ),
        'Transaction Table'[Invoice#] = EARLIER ( 'Transaction Table'[Invoice#] )
            && 'Transaction Table'[Trx Type] = "Charge"
    )
)

    The result will like below:

    Best Regards,

    Teige