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 calcula...
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

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