Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
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.
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! 🙂
Proud to be a Super User!
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.
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! 🙂
Proud to be a Super User!
That was terrific!
Thanks a lot! This solution is, certainly, out of the box!
😀
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |