Forum Discussion

amiller5's avatar
amiller5
Helper II
3 years ago

Count if - Index

Hello - I need help formulating the following Excel formula into the correct Dax syntax: =COUNTIFS(IMIC!A:A,INDEX(B:B,ROW()),IMIC!J:J,">"&'Account Level'!$E$5) IMIC A:A is unique client name IMIC ...
Need Help
amiller5's avatar
amiller5
Helper II
3 years ago

That may have helped a little. 

I added a Date table to relate all my dates. 

I need to create 3 month, 6 month, and 12 months usage count, by client name, for each table (Vue, IE report activity, and IMIC report activity) based off the review month date. 

 

I tried this calc within the Vue table: and got an error when I put in a matrix as a value.

Vue usage 3 months = CALCULATE(SUM(Vue[Fixed Client name]),DATEADD('Date'[Date],-3,MONTH))
Here is how my tables relate.

 

Ashish_Mathur  - any ideas???

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    I do not know what you are trying to do.  Replace SUM(Vue[Fixed Client name]) with DDISTINCTCOUNT(Vue[Fixed Client name])

    • amiller5's avatar
      amiller5
      Helper II
      3 years ago

      I am trying to do the following Excel formula but in PowerBi

      =COUNTIFS(IMIC!A:A,INDEX(B:B,ROW()),IMIC!J:J,">"&'Account Level'!$E$5)

      IMIC A:A is unique client name, indexing column B:B for the matching client name

      IMIC J:J is a date reference 

      Account Level E5 is a specific date within a 3 month timeframe of the date reference in J:J