Forum Discussion

Russ99's avatar
Russ99
Helper I
4 years ago
Solved

Custom column or measure

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 fo...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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's avatar
amitchandak
Super User
4 years ago

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's avatar
Russ99
Helper I
4 years ago

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