Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pcuezze
Frequent Visitor

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.

 

transaction.png

 

 

1 ACCEPTED SOLUTION
TeigeGao
Solution Sage
Solution Sage

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:

Snipaste_2019-04-19_15-22-41.png

Best Regards,

Teige

View solution in original post

1 REPLY 1
TeigeGao
Solution Sage
Solution Sage

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:

Snipaste_2019-04-19_15-22-41.png

Best Regards,

Teige

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.