how to create a column with excel wired formula in Power BI

[xujefferson]

Hi, Community

I got a sunk to create one column with a simple EXCEL fomular in power BI.

The table is like this in EXCEL and i need to create the column "Weekly Opened" in Power BI.

Weekly Opened  is formula and started from [JIRA number start] + Weekly Created - Weekly Resolved.

Saying 9 = 8+(3-2) for 1st line 202340.

If Weekly Opened is smaller than 0, then fill 0 in. But next value need to base on the "0" rather than any sum value as before.

Saying

the line of 202343,  0 = MAX(0, 9+(1-11))

the line of 202344,  2 = MAX(0, 0+(4-2))

and so on....

How can i do in Power BI? Thank you!

	JIRA number start	8
Weekno.	JIRA number	Weekly Created	Weekly Resolved	Weekly Opened
202340		3	2	9
202341		4	3	10
202342		6	7	9
202343		1	11	0
202344		4	2	2
202345		3	2	3
202346		2	7	0
202347		3	1	2

[TheoC]

Hi @xujefferson 

My apologies!  Thank you for letting me know the Target Output!  Here is the calculated column you need (ignore earlier):

Calc Column = 

VAR CurrentWeekNo = [Weekno.]

VAR PreviousWeekNo = CurrentWeekNo - 1

VAR JiraStart = 8

VAR PreviousWeekOpened = 
   
    CALCULATE (
        MAXX (
            FILTER (
                'Table', 
                'Table'[Weekno.] = PreviousWeekNo
            ) ,
            [Weekly Opened]
        ) ,
        ALL ( 'Table' )
    )

VAR InitialValue = IF ( ISBLANK ( PreviousWeekOpened ) , JiraStart , PreviousWeekOpened )

VAR Result = MAX ( 0 , InitialValue + [Weekly Created] - [Weekly Resolved] )

RETURN Result

As you can see, the output in the CalcColumn returns is aligned to desired output. 

Hope this helps mate and apologies again!

Theo

[xujefferson]

Thank TheoC for your great time on my issue!

Not sure if my explanation is clear or not that the column "Weekly Opened" is the new column to be created in Power BI base on the "Weekly no." , "Weekly Create"  and "Weekly Resolve."  😞 

If i loaded your latest solution where you refer to "Weekly Opened" but which is the new column i want to create. 

I am so sorry to waste your time if my explanation is not clear enough or my interpretion is not correct.

Thank you very much again!

[TheoC]

Hi @xujefferson 

If I understand this correctly, you can create the following calculated column:

Weekly Opened = 
VAR _CurrentWeekNo = [Weekno.]
VAR _PreviousWeekNo = _CurrentWeekNo - 1
VAR _PreviousWeekOpened = 
    CALCULATE (
        MAX ( 'Table'[Weekly Opened] ) , 
        'Table'[Weekno.] = _PreviousWeekNo
    )
VAR _JiraStart = [JIRA number start]
VAR _WeeklyCreated = [Weekly Created]
VAR _WeeklyResolved = [Weekly Resolved]
VAR InitialValue = IF ( ISBLANK ( _PreviousWeekOpened ) , _JiraStart, _PreviousWeekOpened )

RETURN

MAX ( 0 , _InitialValue + _WeeklyCreated - WeeklyResolved )

Just make sure to check the table and column names in the formula to match those in your data model. 

Let me know how this goes.

Theo 🙂

[xujefferson]

Thank you Theo!

I tried and the "weekly Opened" is a column to be created. If i put your codes there, it failed with error(A circular dependency was detected for 

MAX ( 'Table'[Weekly Opened] ) 

[TheoC]

@xujefferson I didn't realise that 'Table'[Weekly Opened] was a calculated column.  Can you try the following calculated column:

Weekly Opened = 

VAR _CurrentWeekNo = [Weekno.]
VAR _InitialValue = 

[JIRA number start] + 
    SUMX(
        FILTER(
           'Table',
               'Table'[Weekno.] < _CurrentWeekNo
                ),
           'Table'[Weekly Created] - 'Table'[Weekly Resolved]
         )

VAR _Result = _InitialValue + [Weekly Created] - [Weekly Resolved]

RETURN

    IF ( _Result < 0, 0, _Result)

[xujefferson]

Great Theo! We are closing!

Weekly-Opened is the new column created following your codes. While i added Column "Target" as a reference to see the gap we have.

The gap right now is once you fill "0" into the Weekly-Opened column, the next value should be base on this "0" + Weekly Created - Weekly Resoved rather than the sum-up as before.

That also made me suffered to continue.

Appreciate for all support you gave! Thank you!

[xujefferson]

THe fomular in Excel is very simple like