Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

DAX Measure Suggestion

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?

 

11 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I tried this measure,but not showing any Visual

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you tamerj1 , i will check these two measures