Forum Discussion
Targets/Goals best practice
Hi MaxBell310
Another way to handle this, is to move the matching logic into the data model itself. You can create a small bridge (combination) table that lists all valid Origin–Destination–Product combinations and assign a key to each one. Then connect both your actuals and your targets to this table. That way, when a user makes a selection, the model naturally brings back the correct target without needing complex calculations.
For things like Worldwide targets, you can just include those as rows in the same table and map your data accordingly in Power Query. And for the time mismatch (quarterly/annual vs weekly), you can either spread targets to a lower level or connect them using a common time key like Year or Quarter. Overall, this keeps things simpler, easier to debug, and more stable compared to merging everything or relying too much on DAX.
If there are any deviations from your expectation please let us know we are happy to address.
Thanks.
If I understand your question well, I think it is very similar to the DAX patterns solution to budgeting on different granularities as explained in detail here: https://www.daxpatterns.com/budget/
The problem you are describing has quite some moving parts to consider, which I think are well documented in the pattern and thus it is a bit easier to refer to that than to detail it out here completely.
I will go through this. Thank you.