Dynamic Value using Numeric Parameter in calculating Cashflow Opportunity

[jovendeluna21]

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

[jovendeluna21]

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]

)

[Ritaf1983]

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]

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

[Ritaf1983]

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]

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.

[danextian]

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]

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 · Accepted solution]

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