Excel Index match in DAX

[hari_bi]

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 then i need to search same moth in "FiscalMonth" column after taht need to get respected moth value from "monthsCover " column and finally need to display the same in value new column "MothsCoverMatch".

Here MothCover := Actual/Total is Calcualted Meaure.

Now i need to do same in Dax as Calcualted Measure/Column, Please provide the suggestinos to achieve this one.

[Ashish_Mathur]

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]

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]

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.

[hari_bi]

Hi Ashish Mathur,

Thanks for your replay.

Please find the below path cleaned "Excel Index Match"PBI file.

https://www.dropbox.com/s/ov3gh5i0jpapshv/Excel%20Index%20Match.pbix?dl=0

Thank you.

Hari.

[Ashish_Mathur]

Hi,

In the abridged file that you have shared with me, what is the fiscal month and what is the reporting month?

[hari_bi]

Hi Ashish Mathur,

https://www.dropbox.com/s/osys6n66euka2y0/Excel%20Index%20Match.pbix?dl=0

Please Consider Year Month column from Date Tabe and [ForecastPublishMonth] as Reporting month  from Forecast Date table.

Presently matching data not available.

thaks you.

Hari

[Ashish_Mathur]

Hi,

That link leads to an eroneous page

[hari_bi]

Hi Ashish Mathur,

Please try this link , anyone can access this link no restriction on access.

https://www.dropbox.com/s/osys6n66euka2y0/Excel%20Index%20Match.pbix?dl=0

Thanks,

Hari.

[Anonymous]

Hi All, 

Was there ever a solution for this.  I have the same problem. 

Cheers

R

[v-cherch-msft]

Hi @hari_bi

You may try to create a summarize table first. Then use LOOKUPVALUE Function to create a column. For example:

Table =
SUMMARIZE ( Table2, Table2[Fiscal Month], Table2[Months Cover] )

Column =
LOOKUPVALUE (
    'Table'[Months Cover],
    'Table'[Fiscal Month], Table2[Reporting Month]
)

Regards，

Cherie

[hari_bi]

Hi Cherie,

Thanks for your replay.

Here i am sharing my PBI report with you and please find the Index match page , exactly my requirment is available in Table visual.

https://drive.google.com/drive/my-drive

https://drive.google.com/drive/my-drive

Thanks in Advance

Regards,

Hari.

[v-cherch-msft]

Hi @hari_bi

You may try below measure. If it is not your case,  share your file in Dropbox and post the link here.

lookup =
CALCULATE (
    VALUES ( Table2[Months Cover] ),
    FILTER ( ALL ( Table2 ), Table2[Fiscal Month] = Table2[Reporting Month] )
)

Regards,

Cherie

[hari_bi]

Hi Cherie

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.