Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pedro_Lourenco
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
JarroVGIT
Resident Rockstar
Resident Rockstar

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!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
JarroVGIT
Resident Rockstar
Resident Rockstar

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!

Proud to be a Super User!




That was terrific!

 

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

😀

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.