How to select the yearly average value of a line item from a module that is dimensioned by time (monthly), for the current year, in another module that is not dimensioned by time? Please note, I wish to make the formula dynamic and not use static values like TIME.FY19
I have a "Shipments" module that is having monthly scale, time dimension. Line item "ShipmentRatio %" is a percent formatted metric.
I have another module called "Yearly Assumptions", which is not dimensioned by time.
In the Year Assumptions module, I wish to calculate a line item as follows:
CurrentYrAvgRatio = Shipments.ShipmentRatio %[SELECT: TIME.'FY19']
Please note that ShipmentRatio % Summary and Time Summary methods have been set to "Average"
I don't want to hardcode 'FY19'.
Using Shipments.ShipmentRatio %[SELECT: TIME.'Current Period'] is not helping as it is giving me the first MONTH's value from 2019, instead of FY19 average.
How can this be resolved?
If I am understanding you correctly, you can use a lookup module formatted as Time (Year) with the formula PERIOD(CURRENTPERIODSTART()).
Hope this helps,
Have you tried Movingsum or Timesum?
Both have AVERAGE settings
Thanks for your suggestions! For some reason, my forum topics got classified as spam and then got republished twice.
I follwed the solution in this post:
Rob -- using your solution through PERIOD(CURRENTPERIODSTART()) somehow is not working. PARENT(TIME.Current Period) works.
David -- I had used TIMESUM earlier, but I wanted a quick lookup solution.