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 cash amount that a company will have at its disposal if payment terms with supplier would be at least "N" days, instead of current. It is zero when payment terms exceed "N" days.

 

Currently below is the calculation but I want the 80 to be dynamic number based on the entered numeric parameter.

 

 

 

 

I attached here the pbi copy.

https://drive.google.com/file/d/10H0HMRS9AIp2WpSTtk5rl9buKUz0V5sd/view?usp=sharing

 

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]
)

8 Replies

  • 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]
)
    • 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

    Hi, danextian, I already solved it by modifying your given calculation:

    Adjusted Cashflow Opportunity =
    SUMX (
        ADDCOLUMNS (
            -- Calculated to be evaluated based on these columns
            SUMMARIZE (
                'Table',
                'Table'[Supplier Name],
                'Table'[Net Days],
                'Table'[Spend per Day]
            ),
            "@Cashflow",
                IF(
                    [Opportunity Variable Value] <= [Net Days],
                    0,
                    ( [Opportunity Variable Value] - [Net Days] ) * [Spend per Day]
                )
        ),
        [@Cashflow]
    )