Help Identify New suppliers

[Russ99]

Hi,

I am working with a supplier spend data file and trying to create a "monthly insight report" in which I show who the new suppliers are in the most recent 30 days and how much spend i have with them. I have a column called date paid in my fact table. I was planning to create some sort of measure / column / table that shows me all the suppliers who have first been used in the last 30 days and then  use it to display all of these suppliers in a table with their spend.

i was thinking i would need to create a measure to show when a suppliers first "date paid was" and then filter a visual on when the first date is after a certain point, if so how would this work? I have supplier ID, supplier name, invoice number, category fields, date paid amongst other fields in the fact table.

I also want a table to show suppliers that have been dormant for 1 year (no transactions in last year) i was thinking of using a similar method but using last transaction before or on a certain date rather than first transaction as above.

If anyone can refer me to any content or give me some guidance it would be much appreciated. Ideally this insight report could be automated each month but for the time being i am happy to just rearrange the filters each monthe

[Russ99]

@johnt75 That worked a treat thank you!

[johnt75 · Accepted solution]

I would create a supplier dimension table with columns for first and last transaction date, like

Suppliers = ADDCOLUMNS(
ALLNOBLANKROW('Table'[Supplier ID]),
"First transaction", CALCULATE(MIN('Table'[Invoice date]),
"Last transaction", CALCULATE(MAX('Table'[Invoice date])
)

and create a one-to-many relationship from this new table to your fact table.

You could use simple relative date filtering to show new suppliers where the first transaction date is in the last 30 days, but you would probably need to create a measure to use as a filter to show those where the last transaction date was more than 365 days ago