Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |