Custom column or measure

[Russ99]

Hi Community,

I have a spend report (Fact Table) that shows all transactions for a year. I have supplier code, invoice number, value, entry date and date paid columns. I also have a date table for the fiscal year dates and a supplier dimension table with the supplier code, name and payment terms, this is related to the fact table in a bi directional 1 to many relationship. In the fact table there are multiple lines for each transaction as an invoice will sometimes contain multiple part numbers.

I want to determine weather each invoice is paid late or early in accordance with each suppliers payment terms. I think I would need some sort of IF statement like follows:

If(due date < (entry date + payment terms), "Early", "Late")

I know a measure along these lines wouldnt work as there is no aggregation on the date function. I also dont want to go down the merge queries route to create a custom column because the fact table has nearly a million rows. is there any measure i can use that will tell me weather each invoice is paid early or late? Or any other method i can use without merging queries, if merging queries is the only way, it would be great if you could let me know too.

Thanks a lot!

[v-yanjiang-msft · Accepted solution]

Hi @Russ99 ,

According to your description, I create a sample.

Table:

Supplier:

The two tables are related with the supplier code column.

If you want to check if the invoice number are early or late, just create a measure.

Check =
IF (
    MAX ( 'Table'[due date] )
        < MAX ( 'Table'[entry date] ) + MAX ( 'supplier'[payment terms] ),
    "Early",
    "Late"
)

If you want to count the number of invoice number which is early, create a measure.

Count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[invoice number] ),
    FILTER (
        'Table',
        'Table'[due date]
            < 'Table'[entry date] + RELATED ( 'Supplier'[payment terms] )
    )
)

Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Russ99 ,  You can measure like

Countrows(filter(Table, [due date] < ([entry date] + [payment terms]) ))

Not if you new to take invoice with multiple lines then if all dates are same, then you try

Countrows(filter(values(Table[Invoice]) , Max([due date]) < (Min([entry date]) + Max([payment terms]) ) ))

[Russ99]

Hi Amit,

Thank you so much, I am definately one step closer! The second formula only displays blank but the first formula counts how many invoice line items were only. Unfortunately, it doesnt count the invoices, rather it counts the line items. For example if i have 10 invoices but one has two items, the visual will display 11 (provided all invoices are paid early). I would want the visual to show 10 because there are only 10 unique invoice numbers, even though there are 11 rows in the table.

Thanks again