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]
)
danextian's avatar
danextian
Super User
1 year ago

Hi jovendeluna21 

 

Try this:

 

Adjusted Cashflow Opportunity = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Supplier Name],
            'Table'[Net Days],
            'Table'[Spend]
        ),
        "@Cashflow",
            ( [Opportunity Variable Value] - [Net Days] ) * DIVIDE ( [Spend], 360 )
    ),
    [@Cashflow]
)

 

 

  • jovendeluna21's avatar
    jovendeluna21
    Helper IV
    1 year ago

    Thanks danextian  , it works however can we consider also this "It is zero when Net Days exceed "N" days. For example if I enter 70, Supplier B's Adjusted Cashflow Opportunity should be zero since its Net Days is 80 which is less than the Opportunity Variable entered which is 70.

    • danextian's avatar
      danextian
      Super User
      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]
)