Return absolute value for a range of rows

[rgstevens]

Hi All

PowerBI beginner here - have done beginner training and will do intermediate training in a month or so.

Im attempting to create a custom collumn that will return a value from collumn 2, but the value of the custom collumn is dependent on the index number in an index collumn

Rows 8-37 need to reuturn the 2nd value in column 2

Rows 38 to 47 need to return a null value

Rows 48 to 77 need to return the 42nd value in column 2

Rows 78 to 87 need to return a null value

Rows 88 to 117 need to return the 82nd value in column 2

Rows 118 to 127 need to ruturn a null value

and so on until the end of the data (for thousands of rows)

Any advice greatly apprecaited.

Rob

[v-piga-msft · Accepted solution]

Hi @rgstevens ,

By my tests and research, you could create the calculated columns below to achieve your desired output.

Column 2 = MOD('Data'[Index]-8,40)
Column 3 =
IF (
    Data[Column 2] = 0,
    CALCULATE (
        SUM ( Data[Value] ),
        FILTER ( Data, Data[Index] = EARLIER ( Data[Index] ) - 6 )
    ),
    IF ( Data[Column 2] > 0 && Data[Column 2] <= 29, 0 )
)
Column 3 =
IF (
    Data[Column 2] = 0,
    CALCULATE (
        SUM ( Data[Value] ),
        FILTER ( Data, Data[Index] = EARLIER ( Data[Index] ) - 6 )
    ),
    IF ( Data[Column 2] > 0 && Data[Column 2] <= 29, 0 )
)
Column 5 = CALCULATE(MAX(Data[Column 3]),ALLEXCEPT(Data,Data[Column 4]))

Here is the output.

More details, you could refer to my attachment.

Best  Regards,

Cherry

[rgstevens]

Hi Cherry

Thank you for your work with this. Ive followed it along and can see it works well in the example file you provided. I re-read my original request and see that I misleadingly used the word value. I actually need text returned, Eg. ACH0023. Appologies for this miscommunication on my part.

Rob

[v-piga-msft]

Hi @rgstevens ,

I'm not clear about your data sample, but I think the logic should be the same. Please modify my formulas based on your scenario.

If you need other help, you could create another topic so that people who may have the same question can get the solution directly.

Best  Regards,

Cherry