Hi, hopefully i'll do a good job explaining this.
Your Q1 FY2017 calculation has "shipment months" that aren't in the grouping for them but are used in your calculation.
There are two quick ways to deal with this. You can create a calculated field for a filter that has logic for shipment months and quarters. Like this...
Or you could create two seperate measures for each quarter that has that filter logic built into them. (This way would be more flexible actually because you dont have to apply those filters)
Attached has that calculation for a new filter.
Hopefully this helps.
sl4 dummy.twbx 43.5 KB
That's awesome. thank you. I think I did miss something that you brought up. I have to create a year over year calc after the correct numbers show up. I think the only way to do that is to create two measures so that I can do a Q1 16 - Q1 '17. Can you help with that? Thank you.
Yeah, I'll try to step you through it.
Basically you take the logic from the filter and use it inside a calculated measure(s). So we will create a [TS] and [SN] for each quarter of interest based on the quarter. Also, we can take off filters for month or quarter because we are essentially filtering within our new measure.
So we would make a [TS - Q1 FY2016] that uses logic to only return then [TS] value if it the correct month and quarter, and otherwise return null. It would be:
([Shipment Month] = "AUG FY2016" or [Shipment Month] = "JUL FY2015" or [Shipment Month] = "JUN FY2015" or [Shipment Month] = "MAY FY2015") and [Quarter] = "Q1 FY2016" then [TS]
else null end
Then we would do the same thing for the [NS - Q1 FY2016]....
([Shipment Month] = "AUG FY2016" or [Shipment Month] = "JUL FY2015" or [Shipment Month] = "JUN FY2015" or [Shipment Month] = "MAY FY2015") and [Quarter] = "Q1 FY2016" then [NS]
else null end
Then you would create your AR% speciccifin for the quarter using those two. So the [AR% - Q1 FY2016] would be just like your original but use our new fields...
SUM([NS - Q1 FY2016 ])/sum([TS - Q1 FY2016])
Then you would repeat that with the appropriate logic for as many quarter as you would like. Hopefully this helps, see the attached workbook for more help.
sl4 dummy2.twbx 43.2 KB
This looks great. I'll work on implementing it now. Thanks!!!