I’m building a model and need some relationship help. Here is a picture of the model.
PBI Model.bmp (1.8 MB)
The latest piece I introduced was the Goals table. It’s not possible to create a relationship between Goals and Items because it would be Many-to-Many. An intermediary table called Portfolios was created to enable a One-to-Many relationship to Goals and Items. When trying to show sales versus goal, I used the following DAX calculations.
Total Goals =
CALCULATE (SUMX (Goals, Goals[Target]),
Goal Year to Date =
CALCULATE ([Total Goals], DATESYTD ( Dates[Date] ))
When I select Rep from the Rep Names table and Goal Year to Date, it displays data through September. However, when I replace Rep with Portfolio from the Portfolios table, it shows the entire 2020 goal, not the YTD goal.
Total Sales =
CALCULATE (SUMX ( Billing, Billing[Ship_Dollars]),
Sales Year to Date =
CALCULATE ([Total Sales], DATESYTD( Dates[Date] ) )
How do I get the YTD formula to work when selecting the Portfolio from the intermediary table?