Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
@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]) ) ))
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
63 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |