Need a filter for FY months

Hi community,

I need a formula to filter my module months based on current fiscal year. For example, if my FY is 24 (FY24), I want to filter the module to show all months for FY24 (Jan to Dec).

After that, I want to show text 'Actual' for actual months and text 'Forecast' for forecast months. Below is a illustration of what I want.

Any advice is much appreciated.

Thanks

Kal

Best Answer

  • Prajjwal88
    edited November 5 Answer ✓

    Hi @logikalyan ,

    This can be simply done using a suitable formula where you can write a condition to match the FY input value with the year value for the module dimensioned by time.

    Here's an example for the same,

    Note that you can change your current FY input shown over here to be calculated based on current date or current month or simply using the formula suggested by sobaid.





    I hope this helps.

Answers

  • sobaid
    edited November 5

    Hi @logikalyan ,

    You can find current period,with the formula,in a boolean value,similarly you can find actual period and forecast period based on the booleans you can drive your text or list based condition to show Actual or Forecast


    CurrentPeriod: START() = CURRENTPERIODSTART()
    Forecast Period: START() >= CURRENTPERIODSTART()

    List containing Actual and Forecast list members, you can also levarage systsem module to put these two line items as dropdowns instead of direct reference

    Line Item: if Forecast Period then List.Forecast else Actual
    (or) Line Item: If Forecast Period then SYS00 module.Forecast else SYS00 module.Actual

    Please Let me know if you have questions

  • sobaid
    edited November 6

    Hi @Prajjwal88 , If you could update the formula in SYS01 Lookup module to dynamically update the Year value as the year proceeds, that would be great, here's how you could update it using Period(currentperiodstart()) instead of Item(Time) with Year format

  • Hi @Prajjwal88 & @sobaid,

    I have simply created a text line item in my sys module and referred to actual and forecast month numbers and completed my requirement.

    Thanks both

    Kal

  • @kakocer , user dimension is not suggested or mentioned in implementing above discussed solution. You don't need user dimension (even just in source module) unless your requirement is to filter the target modules (non-user dimensioned) based on user specific selected year.

    Cheers!