Power Bi dynamic filtering of Finance matrix

[SamWhitehouse00]

Hi, 

I have the below data structure:

• Project Table
• Budget Table
• Actuals Table
• FY table (Which is a What If Table)

I am trying to create a matrix where, for each project, it shows the Project Name, a Sum of the total budget in the Rows area and then show the actuals by month with a total actuals spend at the end. I have a slicer that, when a new financial year is selected, the Matrix will update.

However, when selecting a different year, the actuals update to reflect the year but the Total Budget does not update. 

What i want is for the formula to look like the below, 

"Total Budget = CALCULATE(sum(Actuals[Value]),Actuals[FY]= Selected date from slicer)". I have tried to do this but get the below error

Any Help

[Ashish_Mathur]

Hi,

At the very least, create a Calendar Table with a relationship (Many to One and Single) from the Date column in the Actual and Budget tables to the Date column in the Calendar Table.  You will have to create a Date column in the Actual and Budget tables from the Year and Month columns that are there. 

[lbendlin]

Please do not specify table names for measures. These are meaningless.

You need to protect your measure value from the CALCULATE context transition. Put the [FY Value] measure into a variable outside the CALCULATE and then reference by the variable name.

Why is Budget impacting FY2 ?