Forum Discussion

hari_bi's avatar
hari_bi
Icon for Helper I rankHelper I
8 years ago

Excel Index match in DAX

Excel Formula for above one : =INDEX($AM$9:$AM$20,MATCH(D15,$E$9:$E$20,0))   Now i need to implement the same in DAX Calculated Measure. First i need to get month from "ReportingMoth" cloumn...
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi,

 

Write this calculated column formula

 

=CALCULATE(SUM(Data[Months Cover]),FILTER(Data,Data[Fiscal Month]=EARLIER(Data[Reporting Month])))

 

Hope this helps.

  • hari_bi's avatar
    hari_bi
    Icon for Helper I rankHelper I
    8 years ago

    Hi

     

    Thanks for your replay.

     

    https://www.dropbox.com/s/lnvnttc4tvmiq7k/Local%20Copy%20R%26D.pbix?dl=0

     

    Please find the Index Match page, my query is available on table visual.

    Months Cover will avaialebe in Depletions Table and months cover is a calcualted measure not a base measure.

    Fiscal Month (Fiscal Month) available in Date table

    Reporting Month available on Forecast Date table.

     

    Thank you

    Hari.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      8 years ago

      Hi,

       

      I would like to help but am unable to because of the confusion caused by so many other Tables in your file.  Just share only as much information as is necessary to solve the problem.  Remove the unwanted Tables/relationships and only share the tables which are required for solving the problem.