2

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?Snap of Excel Report

Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
Kiran Patel
  • 65
  • 1
  • 7
  • Your excel formula don't work correctly. Row1, Month End = "Y" The condition is not met,that means we go to the second IF. Here you have 01/2020=Report Month and this should give you TRUE. 249 minus value from GT column (also 249). Finally, output should by 0, not 249; Can you explain without formula what you need to achieve? – msta42a Oct 15 '20 at 08:59
  • Thanks for the formula .. this will work .. but before that I have to add one calculated column in power bi dax. In my excel data their is month end column. Like, if it is today's date then, month end=Y, and if date is future and previous, then month end=N. – Kiran Patel Oct 15 '20 at 10:50
  • Can you help me with the formula to apply for month end calculated column? In excel, I have applied mentioned formula in Row1 of Month end column- =IFERROR(INDEX(Table4[MonthEnd],MATCH(CONCATENATE([@[Report Month]],[@[Report Week]]),Table4[Overall],0)),"N") – Kiran Patel Oct 15 '20 at 10:55
  • As you can see in table, W05 is last week of 01/2020, so month end is Y, for rest it is N. Same way, W09 is last week of 02/2020 (Report month), month end= Y. rest is N. Can you help me with the formula I can use in power bi to poplulate end month (N or Y)? – Kiran Patel Oct 15 '20 at 11:07

1 Answers1

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()
)
mkRabbani
  • 16,295
  • 2
  • 15
  • 24