cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Pedro_Lourenco Frequent Visitor
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 dateIDNameCategory
01/31/2007123JohnA
01/31/2007222MaryA
01/31/2007321BobB
02/28/2007123JohnA
02/28/2007321BobB
03/31/2007123JohnA
03/31/2007321BobB
04/30/2007222MaryA
04/31/2007123JohnA
05/31/2007222MaryA

 

 

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
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:

image.png

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:

image.png

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:

image.png

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

image.png

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.

 

https://1drv.ms/u/s!Ancq8HFZYL_aiItDUonqIklYNkn7Nw?e=F2dZF6 

 

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! 🙂


Did I answer your question? Mark my post as a solution!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Super User I
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:

image.png

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:

image.png

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:

image.png

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

image.png

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.

 

https://1drv.ms/u/s!Ancq8HFZYL_aiItDUonqIklYNkn7Nw?e=F2dZF6 

 

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! 🙂


Did I answer your question? Mark my post as a solution!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

View solution in original post

Pedro_Lourenco Frequent Visitor
Frequent Visitor

Re: Calcule the average duration on entry

That was terrific!

 

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

😀

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

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!

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?

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.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors