Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Excel formula to Dax

Hi All need help in converting below Excel formula to Dax

 

=SUMPRODUCT((source[Job Registered Date]>=(EOMONTH(E7,-1)+1))*(source [Job Registered Date]<=(EOMONTH (E7,0)))*(source [engine type]=D11)*(source[LMP]=C8))

Excel formula to Dax
  • BeaBF's avatar
    BeaBF
    2 years ago

    Anonymous HI! Please note that Excel is not directly translatable into DAX, it depends on several factors and variations. This could be a first draft of code, to then be applied to the data. If you want to give me more information, I would be happy to try to help you.

     

    Total =
    CALCULATE(
    COUNTROWS('source'),
    FILTER(
    'source',
    'source'[Job Registered Date] >= DATE(YEAR(E7), MONTH(E7), 1) &&
    'source'[Job Registered Date] <= EOMONTH(E7, 0) &&
    'source'[engine type] = D11 &&
    'source'[LMP] = C8
    )
    )

     

    BBF

2 Replies

  • foodd's avatar
    foodd
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hello Anonymous, and thank you for sharing a question with the Community.  Please remember to adhere to the decorum of the Community Forum when asking a question.

    Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

    https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

    This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    2 years ago

    Anonymous HI! Please note that Excel is not directly translatable into DAX, it depends on several factors and variations. This could be a first draft of code, to then be applied to the data. If you want to give me more information, I would be happy to try to help you.

     

    Total =
    CALCULATE(
    COUNTROWS('source'),
    FILTER(
    'source',
    'source'[Job Registered Date] >= DATE(YEAR(E7), MONTH(E7), 1) &&
    'source'[Job Registered Date] <= EOMONTH(E7, 0) &&
    'source'[engine type] = D11 &&
    'source'[LMP] = C8
    )
    )

     

    BBF