Forum Discussion
Calculated Measure Based on Criteria - Aggregation Issue?
- This isn't a problem with the last year logic in my original reply. Just change the name of DimDate to the appropriate table.
- Really, a goal should be precomputed. In your original question it was simple enough to not care much about how to implement as a measure, but with the details you're revealing, I'd suggest not calculating this as a measure. Your sales last year are known at the time of model refresh, and will never change. These are two defining attributes of values that should be pre-computed. I'd recommend doing this in ETL, either in your source system if you have access/authority to initiate those changes, or as a part of your Power Query step before loading this data to the model. See a sample below for how you might implement this:
Here you go, a nice sample. You can manually enter a multiplier table ('Enter Data') and then copy your sales query and join in the multipliers for each year, then do your multiplication. The result is a FactGoal table that you can then just have some simple measures defined against. This is how we'll set up this sort of reporting for any of our clients.
Hi Greg,
Thank you again for your help! I definitely forgot to mention a couple of key points in my data.
#1 - The database that I use doesn't have any conventional Date Time field. In fact it has absolutely no mention of dates besides the Fiscal Year dim. For this instance it's actually not that bad of a deal because I don't have to use a date math function to figure my fiscal year sales.
#2 - The sales goal is different for each functional group. The IC functional group has a sales goal of 12%, but the others might have a different goal. So when creating my 2015 Sales goal I can't use a blanket [SalesLastYear]*1.12.
How would I go about creating the sales goal measure for only my IC functional groups? In Excel I would just use an IF function, but I couldn't get that to work.
Thank you again for your help!
- This isn't a problem with the last year logic in my original reply. Just change the name of DimDate to the appropriate table.
- Really, a goal should be precomputed. In your original question it was simple enough to not care much about how to implement as a measure, but with the details you're revealing, I'd suggest not calculating this as a measure. Your sales last year are known at the time of model refresh, and will never change. These are two defining attributes of values that should be pre-computed. I'd recommend doing this in ETL, either in your source system if you have access/authority to initiate those changes, or as a part of your Power Query step before loading this data to the model. See a sample below for how you might implement this:
Here you go, a nice sample. You can manually enter a multiplier table ('Enter Data') and then copy your sales query and join in the multipliers for each year, then do your multiplication. The result is a FactGoal table that you can then just have some simple measures defined against. This is how we'll set up this sort of reporting for any of our clients.
Thanks Greg. This is all new to me, so I appreciate the help!