Sum of First Created Date for multiple entities against a date

[Kcloud]

Hi All,

Trying to create a bit of a hack to track the number of new suppliers each month, unfortunately that information isn't tracked on our Database. There is however the first order creation date which is the same date as they are created.

Requirement:

I am trying to create a measure or even column on Table A which will use the first / min Created Date on Table B (Inactive Relationship between the two) to reflect Table A's Created Date.
I then want to be able to count the amount of Suppliers created within a given time frame. 

Example Data:

Table A

SupplierID	SupplierName
1	Alpha
2	Bravo
3	Charlie

Table B

OrderID	SupplierID	OrderCreatedDate
200	1	01/01/2022 10:00:00AM
201		12/02/2022 10:00:00AM
202	1	15/03/2022 10:00:00AM
203	3	30/03/2022 10:00:00AM
204	3	30/03/2022 11:00:00AM
205	2	30/03/2022 11:30:00AM
206	1	02/04/2022 10:00:00AM

Desired Outcome:

When the user selects a date from the slicer, I want to display on a table the number of new created Suppliers in that month.

Month Year	# of new Suppliers	# of new Orders	Average # of Orders per Supplier
January 2022	1	1	1
February 2022	0	1	0
March 2022	2	4	1.3
April 2022	0	1	1

Reasoning:

January had 1 new Supplier from Order ID 200 (Supplier ID 1)

February had 0 new Suppliers but 1 independent Order

March had 2 new Suppliers from from Order ID's 203 & 205 (Supplier ID 1 is excluded as they're recorded in January)

April had 0 new Suppliers as Supplier 1 was recorded in January.

So far I've tried a few different measures and columns but nothing which successfully sums the new Supplier ID's specific to the date.

Feel like it should be obvious, but can't seem to crack it.

Any assistance is greatly appreciated! 

[TomMartens · Accepted solution]

Hey @Kcloud ,

please check this measure, that calculated the no of new supplier:

#no of new suppliers = 
var currentYearMonth = SELECTEDVALUE( 'Date'[Year-Month] )
return
COUNTROWS(
    SUMMARIZE(
        FILTER(
                ADDCOLUMNS(
                    ADDCOLUMNS(
                        SUMMARIZE(
                            CALCULATETABLE(
                                'Orders'
                                , USERELATIONSHIP( 'Supplier'[Supplier Id] , 'Orders'[Supplier Id] )
                            )
                            , 'Supplier'[Supplier Id]
                            , 'Date'[Date]
                        )
                        , "minDate" , CALCULATE( MIN( 'Orders'[Order Created Date] ) , ALL( 'Date' ) )
                    )    
                ,
                "checkYearMonth"  
                , var minDate = [minDate]
                return
                CALCULATE( FIRSTNONBLANK( 'Date'[Year-Month] , 'Date'[Year-Month] ) , 'Date'[Date] = minDate )
                )
                , [checkYearMonth] = currentYearMonth && NOT( ISBLANK( 'Supplier'[Supplier Id] ) )
        )
        , [Supplier Id]
    )
)

This is what the measure returns based on the sample data inside the pbix. The order table does not contain March data (except the row with the missing supplier ID), which means it does not match the expected result in your initial thread - this is very time-consuming.

Hopefully, this provides what you are looking for to tackle your challenge, if not describe the expected result based on the sample data contained in the pbix.

Please be more precise as this will honor the time of the people trying to answer your questions.

Regards,

Tom

[Kcloud]

A thousand thanks, @TomMartens ! 

This is exactly the result I hoped for, and my sincerest apologies for the mixup with the URL and earlier confusion. My attempts at this measure were certainly nowhere near, I am incredibly grateful for your patience and help! Thank you again. 😁

[TomMartens]

Hey @Kcloud ,

this is great to hear!

You can optimize the measure by replacing this line:

CALCULATE( FIRSTNONBLANK( 'Date'[Year-Month] , 'Date'[Year-Month] ) , 'Date'[Date] = minDate )

with this one:

FORMAT( [minDate] , "YYYY MMM" )

Regards,

Tom

[TomMartens]

Hey @Kcloud ,

please create a pbix that contains sample data, but still reflects your data model (tables, relationships, calculated columns, and measures).

I have to admit that I do not understand this

" ...  (Inactive Relationship between the two) to reflect Table A's Created Date."

Please elaborate on the inactive relationship, and why do you need it, maybe it's just an attempt to create the expected outcome.

Next, the OrderCreatedDate contains timestamp information.
As it is a best practice to have a dedicated Calendar table, I'm wondering about two things

• is the column Month Year already part of your existing data model
• Is there any need for the timestamp or will a pure date be sufficient?

Regards,
Tom

[Kcloud]

Hi @TomMartens ,

Please find attached the example pbix:
https://www.dropbox.com/s/gt6ib4et5towm1z/Sample%20Data.pbix?dl=0

The relationship is indeed used in another report, however I felt it relevant to include.

Yes, Month Year is part of the data model, I've replicated this in the example pbix

All the data is imported from an SQL server, so the dates are stored in that format.

[TomMartens]

Hey @Kcloud ,

I will not download from there, I'm sorry.
Use onedrive, google drive, or dropbox.

Regards,

Tom

[Kcloud]

Hi @TomMartens,

Added a Dropbox link. 

Basically just want to add another column to the example table which counts the number of new Suppliers in a date range, based on the first Order Id related to them.

[TomMartens]

Not sure what happened, but when I hit the link, the ufile website opens.

As I mentioned, I will not download from there,

Regards,

Tom

[Kcloud]

Sorry, looks like when I pasted the link it merged the two.

Fixed the original and incase it didn't work: https://www.dropbox.com/s/gt6ib4et5towm1z/Sample%20Data.pbix?dl=0