creating new measure or column ratio_1

[praveenjujare]

Need help to get output as shown in the below table where ratio is measure i need help to create ratio_1 

Date	Investment Company Name	Transaction Type	Ratio	Ratio_1
31-12-2022 00:00	test	Purchase		1
31-01-2023 00:00	test	FV adjustment		1
28-02-2023 00:00	test	FV adjustment		1
31-03-2023 00:00	test	FV adjustment		1
30-04-2023 00:00	test	FV adjustment		1
30-04-2023 00:00	test	Sales		1
31-05-2023 00:00	test	FV adjustment		1
15-06-2023 00:00	test	Shares Adjustment	10	10
30-06-2023 00:00	test	FV adjustment		10
18-07-2023 00:00	test	Sales		10
19-07-2023 00:00	test	Sales		10
20-07-2023 00:00	test	Sales		10
23-07-2023 00:00	test	Sales		10
24-07-2023 00:00	test	Sales		10
25-07-2023 00:00	test	Sales		10
27-07-2023 00:00	test	Sales		10
04-09-2023 00:00	test	Sales		10
05-09-2023 00:00	test	Sales		10
28-09-2023 00:00	test	Sales		10
30-09-2023 00:00	test	Shares Adjustment	5	5
31-10-2023 00:00	test	FV adjustment		5
02-11-2023 00:00	test	Sales		5
06-11-2023 00:00	test	Sales		5
30-11-2023 00:00	test	FV adjustment		5
14-12-2023 00:00	test	Sales		5
18-12-2023 00:00	test	Sales		5
20-12-2023 00:00	test	Sales		5
24-12-2023 00:00	test	Sales		5
25-12-2023 00:00	test	Sales		5

[Angith_Nair · Accepted solution]

Hi @praveenjujare 

Could you please elaborate the requirement along with the calculation?

[Angith_Nair · Accepted solution]

Hi @praveenjujare 

Try the below dax for Ratio_1

Ratio_1 = 
VAR CurrentDate = TableName[Date]
VAR LastAdjustmentDate = 
    CALCULATE(
        MAX(TableName[Date]),
        FILTER(
            TableName,
            TableName[Transaction Type] = "Shares Adjustment" &&
            TableName[Date] <= CurrentDate
        )
    )
VAR LastRatio = 
    CALCULATE(
        MAX(TableName[Ratio]),
        FILTER(
            TableName,
            TableName[Transaction Type] = "Shares Adjustment" &&
            TableName[Date] = LastAdjustmentDate
        )
    )
RETURN
    IF(
        NOT ISBLANK(LastAdjustmentDate),
        LastRatio,
        BLANK()
    )

[Kedar_Pande · Accepted solution]

@praveenjujare 

Create a calculated column in Power BI:

Ratio_1 = 
VAR CurrentDate = 'Table'[Date]
VAR LastSharesAdjustmentDate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
'Table',
'Table'[Date] <= CurrentDate &&
'Table'[Transaction Type] = "Shares Adjustment"
)
)
VAR LastRatio =
CALCULATE(
MAX('Table'[Ratio]),
FILTER(
'Table',
'Table'[Date] = LastSharesAdjustmentDate
)
)
RETURN
IF(
ISBLANK('Table'[Ratio]),
LastRatio,
'Table'[Ratio]
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[praveenjujare]

thanks for help

[Angith_Nair · Accepted solution]

Hi @praveenjujare 

Could you please elaborate the requirement along with the calculation?

[praveenjujare]

For every row after a "Shares Adjustment", ratio_1 will keep the last ratio until the next "Shares Adjustment" occurs, at which point it updates the value of ratio_1.

[Angith_Nair · Accepted solution]

Hi @praveenjujare 

Try the below dax for Ratio_1

Ratio_1 = 
VAR CurrentDate = TableName[Date]
VAR LastAdjustmentDate = 
    CALCULATE(
        MAX(TableName[Date]),
        FILTER(
            TableName,
            TableName[Transaction Type] = "Shares Adjustment" &&
            TableName[Date] <= CurrentDate
        )
    )
VAR LastRatio = 
    CALCULATE(
        MAX(TableName[Ratio]),
        FILTER(
            TableName,
            TableName[Transaction Type] = "Shares Adjustment" &&
            TableName[Date] = LastAdjustmentDate
        )
    )
RETURN
    IF(
        NOT ISBLANK(LastAdjustmentDate),
        LastRatio,
        BLANK()
    )

[praveenjujare]

thanks for help 

[Bibiano_Geraldo]

Hi @praveenjujare , can you be more clear? What ratio_1 calculate? what are conditions?

[praveenjujare]

Ratio_1 =

VAR _INDEX = 'Investment'[Index]  -- Current row index

VAR _LASTNOTSHARESADJUSTMENT =

    CALCULATE (

        MAX ( 'Investment'[Index] ),

        FILTER (

            'Investment',

            'Investment'[Transaction Type] <> "Shares Adjustment"  -- Exclude Shares Adjustment

            && 'Investment'[Index] <= _INDEX

        )

    )

VAR _LASTRATIOAFTERSHARESADJUSTMENT =

    CALCULATE (

        [Ratio],

        FILTER ( 'Investment', 'Investment'[Index] = _LASTNOTSHARESADJUSTMENT )

    )

-- If this row is a Shares Adjustment, return the current Ratio, else return the last valid Ratio

RETURN

    SWITCH(

        TRUE(),

        'Investment'[Transaction Type] = "Shares Adjustment", [Ratio],  

        _LASTRATIOAFTERSHARESADJUSTMENT  

    )