Search code examples
powerbidaxpowerbi-desktoppowerbi-datasourcepower-bi-report-server

Need to apply formula in power bi report

enter image description hereOutputExcel dataenter image description hereI have a table in excel, where there are 3 columns as mentioned. Closed column is calculated column, in which I have applied formula in Row1- IFERROR(IF(B2="","",B2),"") where B2=71 and the output is 71(closed column).

In Row2 of closed column, I have applied formula- IFERROR(IF(01/2020=01/2020, 284-71, 284),"")/ IFERROR(IF(A3=A2, B3-B2, B3),""). Output is 213 and so on...

Same data I have in power bi, Report month and open column. I need to create a calculated column to get the closed column data. Please help me with the formula to apply in power bi.Excel data

Solution

  • You you have a Index or ID column in your data, just use that column. Here I have added a Index column to maintain the ordering between rows and the data looks as below-

    enter image description here

    Now create this below measure-

    Closed = 

VAR previous_row_open = 
CALCULATE(
    MAX(your_table_name[open]),
    FILTER(
        ALLEXCEPT(
            your_table_name,
            your_table_name[report month]
        ),
        your_table_name[Index] = MIN(your_table_name[Index]) - 1
    )
) + 0

VAR previous_row_report_month = 
CALCULATE(
    MAX(your_table_name[report month]),
    FILTER(
        ALLEXCEPT(
            your_table_name,
            your_table_name[report month]
        ),
        your_table_name[Index] = MIN(your_table_name[Index]) - 1
    )
)

RETURN
IF(
    previous_row_report_month = BLANK(),
    MIN(your_table_name[open]),
    MIN(your_table_name[open]) - previous_row_open
)

    Here is the output-

    enter image description here

    Here is the code for Custom Column

    Closed_column = 

VAR current_index = your_table_name_2[Index]

VAR previous_row_open = 
CALCULATE(
    MAX(your_table_name_2[open]),
    FILTER(
        ALLEXCEPT(
            your_table_name_2,
            your_table_name_2[report month]
        ),
        your_table_name_2[Index] = current_index - 1
    )
) + 0

VAR previous_row_report_month = 
CALCULATE(
    MAX(your_table_name_2[report month]),
    FILTER(
        ALLEXCEPT(
            your_table_name_2,
            your_table_name_2[report month]
        ),
        your_table_name_2[Index] = current_index - 1
    )
)

RETURN
IF(
    previous_row_report_month = BLANK(),
    your_table_name_2[open],
    your_table_name_2[open] - previous_row_open
)