DAX Measure Suggestion

[Anonymous]

Hi,

I am trying to build a DAX Measure for Count of number of companies has been created after the first invoice 

We have invoice creation date(Invoice_Create Date)column and company established date(Company_Create_Date) columns, we just need to check that there is no invoice with this company id(Company ID)  on this specific Country Code(Company Code) prior to the company  established date.

I tried this measure

Num of invoices created before company established  =

var allcompanies = VALUES([Company ID])

var Company_established_date_greater_than_invoice_created_date = CALCULATETABLE(Company ID]),

FILTER (

Table name(),

Table name[Company_Create_Date] > Table name[Invoice_Create_date] )

)

return

COUNTROWS(Except(allCompanies,Company_established_date_greater_than_invoice_created_date)).

How can i relate the Country code with Company ID?

[tamerj1]

Hi @Anonymous 
Please try

Num of invoices created before company established =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[Company ID], 'Table'[Country Code] ),
    COUNTROWS (
        FILTER (
            CALCULATETABLE ( 'Table' ),
            'Table'[Company_Create_Date] > 'Table'[Invoice_Create_date]
        )
    )
)

Num of companies created after invoice creation =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[Company ID], 'Table'[Country Code] ),
    IF (
        NOT ISEMPTY (
            FILTER (
                CALCULATETABLE ( 'Table' ),
                'Table'[Company_Create_Date] > 'Table'[Invoice_Create_date]
            )
        ),
        1
    )
)

[Anonymous]

Thank you @tamerj1 , i will check these two measures

[Anonymous]

I need to check both Num of Invoices and num of Companies as well, Primarily i am trying to achieve num of companies/vendors

[amitchandak]

@Anonymous ,

Assume they are from same table. try this measure

Countx(filter(Summarize(Table, Table[Company], "_1", Min(Table[Invoice_Creation Date]), "_2", Min(Table[Company_Create_Date]) ), [_2] >[_1]), [Company])

[Anonymous]

I tried this measure,but not showing any Visual

[Anonymous]

Hi Amit,
Thank You for the response,
But How the company ID is relates to Country Code?

[Anonymous]

Company Id, Country ID, COmpany Est Date and Invoice created date are belongs to same table.

[tamerj1]

@Anonymous 
Does the Country ID refer to the company or to the invoice?

[Anonymous]

yes @tamerj1 i need to check, if there is any invoices created with the company id on a specific country code before the company established date.

[tamerj1]

@Anonymous 
Ok. Are you trying to calculate the number of invoices or the number of companies? I'm asking this because the title contradicts with your dax code attempt.

[Anonymous]

I need both num of invoices and number of companies info but primarily i am working on Number of companies that has been created after the first invoice out of total no of the companies that has been created