Based on Reporting Moth need to get same month of fiscal year months cover value as new column?

[hari_bi]

Hello All,

Thank you for your help to get last month value.

Small update on the same requirement , Please find the below tabale:

Now i have one more column "Reporting Month" added newly.

If Reporting Moth = Dec-17 , i need to search that "Dec-17" in Fiscal Month Column first then what ever the value for that month in "Months Cover" column ,  i need display the same value as new column.

For searching the months we have MonthID as well as DateID cloumn also.

Here i am sharing my PBI file for the same , present in PBI file data not available.

https://www.dropbox.com/s/nnw8mj12d3e2xj0/Month%20Match.pbix?dl=0

Thank you.

Hari

[v-frfei-msft]

Hi @hari_bi,

Based on the picture you shared, I made a sample here. We need to create a calculated column using the formula.

Column = CALCULATE(SUM(Table1[Months Cover]),FILTER(ALL(Table1),Table1[Fiscal Month]=MAX(Table1[Reporting Month])))

Then we can get the result as we need.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/kh5grvrhepxx3yb/Based%20on%20Reporting%20Moth%20need.pbix?dl=0

Regards,

Frank

[hari_bi]

Hi Frank,

Thank you sio much for your help in DAX.

Small query on the same , if i have 2 year data in my report only one match i have but this measure dispalys the same vales to my entire data.

My Question is how to restict this by Year wise.

If year 2018 any month matches will display the values other wise will display the blank values.

Can you help me on this .

Thanks in Advance.

Regards,

Hari.

[v-frfei-msft · Accepted solution]

Hi @hari_bi,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,

Frank