PBI Matrix to display all months in column where values are within selected year

[Anonymous]

Hello,

I have a standard DATE table connected to a Fact table in which I want to calculate the values of a column, 'FACT'[Attributes]', for each Month, i.e January, February, March, etc. on a matrix visual. We are connecting this PBI report to a propietary .Net site in which the .Net site is controlling the Date file by connecting to the 'DATE'[Date] field in PBI using the "MM/DD/YYY" format. 

For a given time period such as "05/01/2020", I would like to show the Monthly values for each [Attribute] for all 12 months within the year 2020. However, my DAX formula below includes ALL years (2019 and 2020) instead of  just 2020.I am using an additional table which includes Month_Name and Month_Sort# to ensure all months are displaying on the matrix. My Dax calculation is:

Month_Value =

var Year = YEAR(SELECTEDVALUE('DATE'[DATE]))

return

CALCULATE( [MTD_Value] ,
ALL ('DATE'[DATE]) ,
YEAR('FACT'[YEAR] = Year))

//MTD_Value = SUM('FACT'[Value])

Anyone know how to resolve this?

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Like this?

Don't create relationship between the two tables.

Maybe my .pbix file can help you, please refer to it.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.