Last N Days for Week/Month/YTD on multiple columns

[BrianaHop]

I am trying to generate a report that shows the AVERAGE days between two columns and do it for multiple column sets within a report. The goal is to show average days between steps of a process, so Average Days it takes to get from Step 1 to Step 2, Average Days it takes to get from Step 2 to Step 3, and so on. I also need it to be broken down by person. Below is an example of the end goal: 

AVG DAYS FROM STEP 1 TO STEP 2
NAME	LAST 7 DAYS	LAST 30 DAYS	YTD
NAME 1	X	X	X
NAME 2	X	X	X
NAME 3	X	X	X
TOTAL AVG DAYS	XX	XX	XX

AVG DAYS FROM STEP 2 TO STEP 3
NAME	LAST 7 DAYS	LAST 30 DAYS	YTD
LO 1	X	X	X
LO 2	X	X	X
LO 3	X	X	X
TOTAL AVG DAYS	XX	XX	XX

The data is coming from an excel file, although once i figure out the math we are going to automate it to come from SQL instead. The table looks similar to this: 

Day Created	Step 1 Reached on	Step 2 Reached on	Step 3 Reached on	Step 4 Reached on
1/1/2020	1/15/2020	1/30/2020	2/2/2020
1/2/2020	1/16/2020	1/31/2020	2/15/2020
1/3/2020	1/17/2020	2/1/2020	2/15/2020
1/4/2020	1/18/2020	2/2/2020
7/8/2020	7/10/2020	7/16/2020
7/9/2020	7/30/2020	8/5/2020
7/10/2020	7/15/2020
7/29/2020	8/5/2020
7/30/2020	8/7/2020
8/1/2020
8/8/2020

The problem I am running into is figuring out a way to AVERAGE the results.  I found a formula to give me the values within N days (below) which required me to create a column to calculate the Days Between each step and then a separate column to Calculate the total days between each step. Then I used the Total Days to Step XX for the "Last N Days" calculation (below). 

Past 30 days =
CALCULATE([Total Days to Step 1],
 FILTER( ALL( Data[Day Step 1 Achieved] ),
 Data[Day Step 1 Achieved] > Today() - 30 &&
 Data[Day Step 1 Achieved] <= Today() ) )

I tried adding this as a column, but if I do that it will not let me create the same calculation for any other step, since it gets a circular dependancy error. I am fine with using measures but I dont know how to get an AVERAGE out of a measure that can be broken into Past 7 days, Past 30 days, etc. I also have blank values in every Step column, since many steps arent achieved yet, just fyi. I'm relatively new at Power Bi so please help. I've been trying to get this figured out for 3 days with no success. 

[v-alq-msft]

Hi, @BrianaHop 

You may go to 'Query Editor'=>'Transform', make all step column selected, click 'unpivot column'. The pbix file is attached in the end.

You may create measures like below.

Last 7 = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Step],
    "Result",
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[Value]>TODAY()-7&&
            'Table'[Value]<=TODAY()&&
            'Table'[Step]=EARLIER('Table'[Step])
        )
    )
)
return
SUMX(
    tab,
    [Result]
)

Last 30 = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Step],
    "Result",
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[Value]>TODAY()-30&&
            'Table'[Value]<=TODAY()&&
            'Table'[Step]=EARLIER('Table'[Step])
        )
    )
)
return
SUMX(
    tab,
    [Result]
)

Result:

Best Regards

Allan

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

[Greg_Deckler]

@BrianaHop - I would unpivot your "step" columns. You could then have a single column that calculated the days between steps using EARLIER. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Once you had a single column, it would be very easy to just use the default AVERAGE aggregation and you could filter/slice however you want.

[BrianaHop]

@Greg_Deckler  Unfortunately I send this report out in a PDF copy to all of our sales reps so I need for the data to be showcased in a single view , which is why we went with the pivot view. Only our management team reviews the Dashboards themselves and uses the tools available in Power BI. 

[Greg_Deckler]

@BrianaHop - The format of the data as a source does not dictate how you present the data in the report. I can almost guarantee that you will find it far easier to do what you are trying to do. You can always create a table visualization off of the data or a matrix visualization to "pivot" that data or present the data however you wish. 

[BrianaHop]

@Greg_Deckler  The data comes directly from a software we use, so there is no way to unpivot it at the source level. Is there any way to unpivot in the Power Query? 

[Greg_Deckler]

@BrianaHop - Yes, that is what I was referring to, in Power Query, select the columns and then right-click and then Unpivot. There is also a DAX Unpivot but I would use the Power Query. https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/m-p/574832#M256