Forum Discussion
Calculated Measure Based on Criteria - Aggregation Issue?
- 10 years ago
- 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.
Dear Sir,
If you are using same measure with criteria like
last Year product sales = calculate(SUM(PBI_MR_001[Product_Sale]), filter(PBI_MR_001, PBI_MR_001[Year_Id])=1)), Here 1 for last year. Current Year product sales = calculate(SUM(PBI_MR_001[Product_Sale]), filter(PBI_MR_001, PBI_MR_001[Year_Id])=2))
| Year_ID | Year |
| 1 | 2015 |
| 2 | 2016 |
, and product table is
| Product | Product_sale | Year_Id |
| Tomato | Rs 2000 | 1 |
| Beat | Rs 5000 | 1 |
| carrot | Rs 15000 | 1 |
| Tomato | Rs 12000 | 2 |
| Beat | Rs 50000 | 2 |
| carrot | Rs 35000 | 2 |
here is measure is working fine , But when evere i will use slicer for year , measures gives only selected years value and another is blanck , Please suggest how can I solve this.
thanks
vilas jadhav