Need If formula to apply in Power bi dax for a report

Question

I have a report in excel as shown in table where there are 7 columns. "Backlog" column is calculated column, in which, I have applied formula in Row1 of Backlog col.

=IFERROR(
    IF(
        Month End="Y",
        ((IFERROR(IF(01/2020=Report Month,249 - GT,249),""))-Open),
        IFERROR(IF(01/2020=Report Month,249 - GT,249),"")
    ),
    ""
) 

The output I got is 249 (as shown in Backlog column).

Now, I want to apply the formula in Power bi dax to get Backlog column. In power bi table, I only have Report Month, Report week, Month End, Closed, Open and GT column. Please let me know what formula I can use to get backlog column?

Answer 1

Here is the DAX code for you-

Please adjust the condition MIN(your_table_name[Month]) = MIN(your_table_name[Month]) I have used for just your reference. the second part is your Report Month in the given code. You can replace this with your expected column from the table.

Remember, this is just sample code with DAX syntex for your reference only. You need to use this logic now for your scenario.

Backlog =
IFERROR(
    IF(
        MIN(your_table_name[Month End])="Y",
        IFERROR(
            IF(
                MIN(your_table_name[Month]) = MIN(your_table_name[Month]),
                249 - MIN(your_table_name[GT]),
                249
            ),
            BLANK()
        ) - MIN(your_table_name[Open]),
        IFERROR(
            IF(
                MIN(your_table_name[Month]) = MIN(your_table_name[Month]),
                249 - MIN(your_table_name[GT]),
                249
            ),
            BLANK()
        )
    ),
    BLANK()
)