Calculate de division of one row vs other row with conditionals

[alfertab]

Hi everybody, I'm having some troubles with a calculation I need to do and I would like to know if someone knows what to do.

Basically I have the following table with daily data of different countries.

Country	Date	Income	Outcome	Percentage
England	29/04/2023	$8,000	$6,000
China	29/04/2023	$9,500	$7,500
Mexico	29/04/2023	$10,000	$8,000
England	30/04/2023	$7,500	$9,000
China	30/04/2023	$8,500	$7,000
Mexico	30/04/2023	$10,500	$9,000
England	01/05/2023	$11,000	$8,000
China	01/05/2023	$10,500	$7,500
Mexico	01/05/2023	$14,500	$11,000

What I need to calculate is the Percentage of the Outcome compared to the next day's income; in case there is no data for the next day, the result of the outcome/income for that same day must be calculated. For example, the value for England(29/04/2023) must be calculated as following: 

Another example could be the value for China (30/04/2023):

The last example could be the value for Mexico (01/05/2023), where we don't have data for the next day:

The calculation needs to be an extra column on the table. At first I was using Index in Power Query, but the problem is that in some future there may be data from more countries and I think using index there would be errors. Does anyone know how to do it? It could be as a column calculated in Power Query or in Power BI. Thanks. 

[lbendlin · Accepted solution]

Percentage =
VAR d = [Date]
VAR c = [Country]
RETURN
    DIVIDE (
        [Outcome],
        COALESCE (
            CALCULATE (
                SUM ( 'Table'[Income] ),
                ALL ( 'Table' ),
                'Table'[Country] = c,
                'Table'[Date] = d + 1
            ),
            [Income]
        ),
        0
    )

[alfertab]

@lbendlin @v-weiyan1-msft Thanks both of you for the answers :D. Both methods were usefull and worked. Thanks a lot.

[v-weiyan1-msft · Accepted solution]

Hi @alfertab ,

@lbendlin nice method! Thank you, for your quick response and the solution provided.
And based on the sample and description you provided, you can also try code as below to create a Calculated column.

Percentage = 
VAR CurrentCountry = 'Table'[Country]
VAR CurrentOutCome = 'Table'[Outcome]
VAR NextDate = NEXTDAY('Table'[Date])
VAR NextIncome =
    CALCULATE (
        MAX ( 'Table'[Income] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Country] = CurrentCountry
                && 'Table'[Date] = NextDate
        )
    )
RETURN
    IF (
        ISBLANK ( NextIncome ),
        DIVIDE ( CurrentOutCome, 'Table'[Income] ),
        DIVIDE ( CurrentOutCome, NextIncome )
    )

Result is as below.

Best Regards,
Yulia Yan

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

[lbendlin · Accepted solution]

Percentage =
VAR d = [Date]
VAR c = [Country]
RETURN
    DIVIDE (
        [Outcome],
        COALESCE (
            CALCULATE (
                SUM ( 'Table'[Income] ),
                ALL ( 'Table' ),
                'Table'[Country] = c,
                'Table'[Date] = d + 1
            ),
            [Income]
        ),
        0
    )