Forum Discussion

jovendeluna21's avatar
jovendeluna21
Helper IV
1 year ago
Solved

Dynamic Value using Numeric Parameter in calculating Cashflow Opportunity

Hi, I need help instead of fixing the opportunity to 80 days, can we input a variable using numeric parameter and the cashflow opportunity will adjust?   Cashflow Opportunity - indicates the total...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Change your formula to this

    Adjusted Cashflow Opportunity = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Supplier Name],
            'Table'[Net Days],
            'Table'[Spend]
        ),
        "@Cashflow",
           MAX( ( [Opportunity Variable Value] - [Net Days] ), 0 ) * DIVIDE ( [Spend], 360 )
    ),
    [@Cashflow]
)
Ritaf1983's avatar
Ritaf1983
Super User
1 year ago

Hi jovendeluna21 

You need to create measures instead of calculated columns to use a parameter , for example :

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

  • jovendeluna21's avatar
    jovendeluna21
    Helper IV
    1 year ago

    Thanks Ritaf1983 , it works however can we consider also this "It is zero when Net Days exceed "N" days.

    • Ritaf1983's avatar
      Ritaf1983
      Super User
      1 year ago

      can you give more details about what you mean...it sounds like just to ad OR ...and one more condition, 
      but I am not sure that I understood you correctly.

      • jovendeluna21's avatar
        jovendeluna21
        Helper IV
        1 year ago

        For example, If I enter 70, the cashflow opportunity for Supplier A should be 400,000. Calculated as the Opportunity variable minus the Net Days multiply by the Spend per Day. ((70-30)*10,000).

        For Supplier B, the cashflow opportunity should be 0, since the Net Days of 80 is greater than the Opportunity Variable of 70.

        For the total, it should be 400,000.