How can I calculate measures for unpivoted data in power BI?

[Anonymous]

I have the following unpivoted data in Power BI that I wish to get the number of working days between each process step, and then find the average working days for each account code

Since the data repeats, what is the best method of tackling this data problem? DAX gives errors for measure saying it cannot determine a single value

Do I simply need to pivot it, like below, or is this data just not workable?

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

According to my understanding, you want to calculate the diff days between two steps of each accound code, right?

Please follow these steps:

1. Add a flag column

Flag = SWITCH([Process Step],"Upload",1,"Move",2,"Submit",3,"Review",4,"Approve",5) 

2. Add a Rank column

Rank = 
VAR a = [Account Code]
var b=[Process Change Date]

VAR t1 =
    FILTER ( ALL ( 'Table' ), 'Table'[Account Code] = a )
var t2=
    FILTER ( ALL ( 'Table' ), 'Table'[Account Code] = a && 'Table'[Process Change Date]=b )
RETURN
    RANKX (
        t1,
        RANKX ( t1, [Process Change Date],, ASC, SKIP ) * 100
            + RANKX ( t2, [Flag],, ASC, SKIP ) * 10,
        ,
        ASC,
        SKIP
    )

Then calculate the diff and average(measure):

Datediff =
VAR pre =
    CALCULATE (
        MAX ( 'Table'[Process Change Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Account Code] = MAX ( 'Table'[Account Code] )
                && 'Table'[Rank]
                    = MAX ( 'Table'[Rank] ) - 1
        )
    )
RETURN
    DATEDIFF ( pre, MAX ( 'Table'[Process Change Date] ), DAY )

average working days =
AVERAGEX (
    FILTER ( 'Table', 'Table'[Account Code] = MAX ( 'Table'[Account Code] ) ),
    [Datediff]
)

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Add an index column and then try to pivot and check.

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

[Anonymous]

@amitchandak Do I add the index column before the pivot or after?