Calculate 'Red' Backlog Ageing with the date when it was first reported

[navedkhan]

Hi All - 

Am looking for URGENT help in PowerBi with calculation of Backlog (for 'Red' items only). That is if on a given date Backlog column shows 'Red' from that date till the date backlog column turns other color (Amber or Green), i need to calculate ageing from that date (excluding weekends)

Below is the sample data and last 2 columns is what am trying to arrive at (through DAX column)

Pls reply with DAX logic only so that i can build a loop in my table.

Date	Team Name	Backlog	First Occurance Date (Red)	Backlog Ageing (Red)
01 Apr 19	ABC	Red	01 Apr 19	1
01 Apr 19	BCD	Green	01 Apr 19	0
02 Apr 19	ABC	Red	01 Apr 19	2
02 Apr 19	BCD	Red	02 Apr 19	1
03 Apr 19	ABC	Red	01 Apr 19	3
03 Apr 19	BCD	Amber	-	0
03 Apr 19	ABC	Green	-	0
05 Apr 19	ABC	Red	05 Apr 19	1
05 Apr 19	BCD	Red	05 Apr 19	1

Please guide so that i can publish this report involving senior stakeholders. I have researched a lot on this community (since 3-4 days) but in vain.

@Zubair_Muhammad 

[Zubair_Muhammad · Accepted solution]

@navedkhan 

Try this column

Ageing =
VAR MyDates =
    ADDCOLUMNS (
        GENERATESERIES (
            SWITCH (
                FALSE (),
                ISBLANK ( Table1[First Occurrence] ), Table1[First Occurrence],
                1
            ),
            Table1[Date]
        ),
        "Day", WEEKDAY ( [Value], 2 )
    )
RETURN
    IF ( [Backlog] = "red", COUNTROWS ( FILTER ( mydates, [Day] <= 5 ) ) )

[Zubair_Muhammad · Accepted solution]

@navedkhan 

Try this modification

Ageing2 =
VAR MyDates =
    ADDCOLUMNS (
        GENERATESERIES (
            SWITCH (
                FALSE (),
                ISBLANK ( Table1[First Occurrence] ), Table1[First Occurrence],
                1
            ),
            Table1[Date]
        ),
        "Day", WEEKDAY ( [Value], 2 )
    )
RETURN
    IF (
        [Backlog] = "red",
        COUNTROWS (
            FILTER ( mydates, [Day] <= 5 && NOT [Value] IN VALUES ( BANKHOLIDAYS[dates] ) )
        )
    )

[Zubair_Muhammad]

@navedkhan 

Give this calculated column a shot

First Occurrence =
VAR Dif_Color =
    MAXX (
        TOPN (
            1,
            FILTER (
                Table1,
                [Team Name] = EARLIER ( [Team Name] )
                    && [Date] < EARLIER ( [Date] )
                    && [Backlog] <> "Red"
            ),
            [Date], DESC
        ),
        [Date]
    )
RETURN
    SWITCH (
        TRUE (),
        [Backlog] <> "Red", BLANK (),
        Dif_Color = BLANK (), MINX ( FILTER ( Table1, [Team Name] = EARLIER ( Table1[Team Name] ) ), [Date] ),
        MINX (
            FILTER (
                Table1,
                [Team Name] = EARLIER ( Table1[Team Name] )
                    && [Date] > Dif_Color
            ),
            [Date]
        )
    )

[navedkhan]

@Zubair_Muhammad 

that worked like a magic !! much appreciate..

one more request - how to calculate last column (ageing) after excluding weekends from today?

[Zubair_Muhammad · Accepted solution]

@navedkhan 

Try this column

Ageing =
VAR MyDates =
    ADDCOLUMNS (
        GENERATESERIES (
            SWITCH (
                FALSE (),
                ISBLANK ( Table1[First Occurrence] ), Table1[First Occurrence],
                1
            ),
            Table1[Date]
        ),
        "Day", WEEKDAY ( [Value], 2 )
    )
RETURN
    IF ( [Backlog] = "red", COUNTROWS ( FILTER ( mydates, [Day] <= 5 ) ) )

[navedkhan]

---

---

@Zubair_Muhammad wrote:

@navedkhan 

Try this column

Ageing =
VAR MyDates =
    ADDCOLUMNS (
        GENERATESERIES (
            SWITCH (
                FALSE (),
                ISBLANK ( Table1[First Occurrence] ), Table1[First Occurrence],
                1
            ),
            Table1[Date]
        ),
        "Day", WEEKDAY ( [Value], 2 )
    )
RETURN
    IF ( [Backlog] = "red", COUNTROWS ( FILTER ( mydates, [Day] <= 5 ) ) )

---

Hi @Zubair_Muhammad 

need your small help again buddy to factor out UK Bank Holidays from the above Backlog Ageing DAX measure that you have thankfully written for me?

I want my Backlog Ageing to also ignore those days when there is bank holiday as per below list !!

Days	Theme
1	New Year’s Day: Tuesday 1 January
2	New Year’s Holiday: Wednesday 2 January (Scotland only)
3	St Patrick’s Day: Monday 18 March (Northern Ireland only)
4	Good Friday: Friday 19 April
5	Easter Monday: Monday 22 April (England, Wales and Northern Ireland only)
6	Early May bank holiday: Monday 6 May
7	Spring bank holiday: Monday 27 May
8	Battle of the Boyne: Friday 12 July (Northern Ireland only)
9	Summer bank holiday: Monday 5 August (Scotland only)
10	Summer bank holiday: Monday 26 August (England, Wales and Northern Ireland only)
11	St Andrew’s Day: Monday 2 December (Scotland only)
12	Christmas Day: Wednesday 25 December
13	Boxing Day: Thursday 26 December

Possible? Please HELP ASAP.

[Zubair_Muhammad]

@navedkhan 

I think you can use EXCEPT function to exclude these holidays.

Something like this

IF ( [Backlog] = "red", COUNTROWS ( EXCEPT(FILTER ( mydates, [Day] <= 5 ),VALUES(UKBANKHOLIDAYS)) ) )

[navedkhan]

write a new column OR ammend existing logic you gave to me earlier?

@Zubair_Muhammad 

Ageing =
VAR MyDates =
    ADDCOLUMNS (
        GENERATESERIES (
            SWITCH (
                FALSE (),
                ISBLANK ( Table1[First Occurrence] ), Table1[First Occurrence],
                1
            ),
            Table1[Date]
        ),
        "Day", WEEKDAY ( [Value], 2 )
    )
RETURN
    IF ( [Backlog] = "red", COUNTROWS ( FILTER ( mydates, [Day] <= 5 ) ) )

@zubair

[Zubair_Muhammad]

@navedkhan 

Assuming BankHolidays are stores in a separate Table named "BankHolidays"  and column named "Dates"

Ageing =
VAR MyDates =
    ADDCOLUMNS (
        GENERATESERIES (
            SWITCH (
                FALSE (),
                ISBLANK ( Table1[First Occurrence] ), Table1[First Occurrence],
                1
            ),
            Table1[Date]
        ),
        "Day", WEEKDAY ( [Value], 2 )
    )
RETURN
    IF (
        [Backlog] = "red",
        COUNTROWS (
            EXCEPT ( FILTER ( mydates, [Day] <= 5 ), VALUES ( BANKHOLIDAYS[dates] ) )
        )
    )

[navedkhan]

@Zubair_Muhammad 

It didn't work buddy. when i added that condition further to the earlier logic i get this error;

Error = "Each table argument of 'EXCEPT' must have the same number of columns."

I have added a new table with UK Public Holidays in there and tried to use that column in this nested DAX logic

Pls suggest a solution to counter above error?

[navedkhan]

@Zubair_Muhammad 

any help on this would be immensely appreciated... 

Anyone??

[Zubair_Muhammad · Accepted solution]

@navedkhan 

Try this modification

Ageing2 =
VAR MyDates =
    ADDCOLUMNS (
        GENERATESERIES (
            SWITCH (
                FALSE (),
                ISBLANK ( Table1[First Occurrence] ), Table1[First Occurrence],
                1
            ),
            Table1[Date]
        ),
        "Day", WEEKDAY ( [Value], 2 )
    )
RETURN
    IF (
        [Backlog] = "red",
        COUNTROWS (
            FILTER ( mydates, [Day] <= 5 && NOT [Value] IN VALUES ( BANKHOLIDAYS[dates] ) )
        )
    )

[navedkhan · Accepted solution]

@Zubair_Muhammad 

thanks a lot brother, it did work this time... will check on the logic for couple of days while said report is in production and revert if anything unusual crops up.

but till then many thanks ,... much appreciate. 🙂

[Greg_Deckler]

So, you want to use EARLIER. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...