cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

Calcule the average duration on entry

Hi all,

I'm trying to calculate the average period(in months) that a particular category of entry stays on my Database.

My Database works like a newsletter database. It is exported monthly and shows every person listed in that month and when someone unsubscribes, it is not present in the next month register. But sometimes, it's not rare to observe people that unsubscribe the service but some time later sign up for the service again.

I will show how my data is organized by the following example:

 Export date ID Name Category 01/31/2007 123 John A 01/31/2007 222 Mary A 01/31/2007 321 Bob B 02/28/2007 123 John A 02/28/2007 321 Bob B 03/31/2007 123 John A 03/31/2007 321 Bob B 04/30/2007 222 Mary A 04/31/2007 123 John A 05/31/2007 222 Mary A

So... calculating the average period of the category A:

I know that the first time Mary shows on my Date base was in jan/07 and as she is not in feb/07 we presume that she stayed only 1 month. She returns in apr/07 and stays until my last actualization that was on 05/31/2007.

Resuming Mary:

first time --> stays 1-month

second time --> staying until now 2-months

John was appearing in my base since jan/07 and left on abr/07, so he's stayed for 4 months

To calculate the average period for category A, it's done
(1+2+4)/3=3,5 Months on average.

In the example, Category B is simpler: 3 months

Finally, my intention is to show those averages in a visual card visual.

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I

Re: Calcule the average duration on entry

I love these questions, they really make you think on how to approach the problem as a whole 🙂 Fun fun fun.

Anyway, this is my solution. First, I loaded your mockup data into powerbi (big thank you for providing that!). I added a calculated column which indicates if the current row was the last row in a streak for that person (ID) in that category. The calculated column DAX is as follows:

``````IsLastMonth? =
VAR _curID = Table4[ID]
VAR _curCat = Table4[Category]
VAR _curExportDate = Table4[Export date]
VAR _hasNextMonth = COUNTROWS(FILTER(Table4, Table4[Export date] = EOMONTH(_curExportDate, 1) && Table4[ID] = _curID && Table4[Category] = _curCat))
RETURN
IF(_hasNextMonth = 1, FALSE, TRUE)``````

Results in:

Next stop was to add a calculated column that gives an index to the occurence of an ID in a specific category. Basically; I want to show 2 for every row that Mary returned later (april and may rows). This way, we can distinct these rows from earlier rows. The DAX is this:

``````IsLastMonth? =
VAR _curID = Table4[ID]
VAR _curCat = Table4[Category]
VAR _curExportDate = Table4[Export date]
VAR _hasNextMonth = COUNTROWS(FILTER(Table4, Table4[Export date] = EOMONTH(_curExportDate, 1) && Table4[ID] = _curID && Table4[Category] = _curCat))
RETURN
IF(_hasNextMonth = 1, FALSE, TRUE)``````

(to be honest, this is the part I was most proud of, I had a big smirk on my face when I got it right haha), resulting in this:

Now that we have this 'index per category per ID' column, we can create a calculated table as a summary table of the above where we want the count of rows for every category, ID and IndexPerIdAndCategory, with the following DAX:

``Table4AdjecentRowCount = SUMMARIZECOLUMNS(Table4[Category], Table4[ID], Table4[IndexPerIDandCategory], "Count", COUNTROWS(Table4))``

Resulting in this table:

From here, you can create a table visual with column Category and the average of the Count column:

By the way, you stated the following for A: "(1+2+4)/3=3,5 Months on average" This is clearly incorrect; 7 / 3 = 2.33 (see my table above).

Anyway, here is the PBIX, this was a fun one and now it is time for bed.

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

2 REPLIES 2
Super User I

Re: Calcule the average duration on entry

I love these questions, they really make you think on how to approach the problem as a whole 🙂 Fun fun fun.

Anyway, this is my solution. First, I loaded your mockup data into powerbi (big thank you for providing that!). I added a calculated column which indicates if the current row was the last row in a streak for that person (ID) in that category. The calculated column DAX is as follows:

``````IsLastMonth? =
VAR _curID = Table4[ID]
VAR _curCat = Table4[Category]
VAR _curExportDate = Table4[Export date]
VAR _hasNextMonth = COUNTROWS(FILTER(Table4, Table4[Export date] = EOMONTH(_curExportDate, 1) && Table4[ID] = _curID && Table4[Category] = _curCat))
RETURN
IF(_hasNextMonth = 1, FALSE, TRUE)``````

Results in:

Next stop was to add a calculated column that gives an index to the occurence of an ID in a specific category. Basically; I want to show 2 for every row that Mary returned later (april and may rows). This way, we can distinct these rows from earlier rows. The DAX is this:

``````IsLastMonth? =
VAR _curID = Table4[ID]
VAR _curCat = Table4[Category]
VAR _curExportDate = Table4[Export date]
VAR _hasNextMonth = COUNTROWS(FILTER(Table4, Table4[Export date] = EOMONTH(_curExportDate, 1) && Table4[ID] = _curID && Table4[Category] = _curCat))
RETURN
IF(_hasNextMonth = 1, FALSE, TRUE)``````

(to be honest, this is the part I was most proud of, I had a big smirk on my face when I got it right haha), resulting in this:

Now that we have this 'index per category per ID' column, we can create a calculated table as a summary table of the above where we want the count of rows for every category, ID and IndexPerIdAndCategory, with the following DAX:

``Table4AdjecentRowCount = SUMMARIZECOLUMNS(Table4[Category], Table4[ID], Table4[IndexPerIDandCategory], "Count", COUNTROWS(Table4))``

Resulting in this table:

From here, you can create a table visual with column Category and the average of the Count column:

By the way, you stated the following for A: "(1+2+4)/3=3,5 Months on average" This is clearly incorrect; 7 / 3 = 2.33 (see my table above).

Anyway, here is the PBIX, this was a fun one and now it is time for bed.

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

Frequent Visitor

Re: Calcule the average duration on entry

That was terrific!

Thanks a lot! This solution is, certainly, out of the box!

😀

Announcements

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.