Forum Discussion
Calculating Number of Records with multiple variables
I have been given a complicated problem to solve and I am at a loss.
Goal:
1) Calculate the number of active records by month.
2) Calculate the number of new records (onboards) by month.
3) Calculate the number of records that went inactive (offboards) by month.
4) Calculate the number of records that changed due to certain types of migration by month.
Example 1 of complexity
This record originally onboarded on 2/26/2014. Its existance will start being added to the total as of that month.
On 4/28/2016, it went through a migration, but remained active.
Active Record Count = same
Migration Record Count +1 for month of 4/2016
On 10/25/2016, the record went officially inactive. Its existance will end as of 9/30/2016
Example 2 of Complexity
This record went through 2 separate migrations at different times. It is also currently still active. Would need to show the original onboard date, continue to include it in the overall record count from 1/2016 forward, but would need to tally both migration points in their given months (for their given reasons).
Example 3 of Complexity
This record went dormant between 5/22 and 11/23. Would need to show the original onboard date, continue to include it in the overall record count from 6/18 to 4/22, include it in the offboard count for 5/22, include it on the onboarded count for 11/23 and the overall record count from 11/23 forward.
See attached for something to get you started. It has both the amortization logic (spelling out the months) and a custom visual that can show date ranges.
3 Replies
- lbendlin
Super User
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 Thank you lbendlin for the reminder. I completely forgot to include that.
PropertyOperationsKey AccountingID PropertyID2 ManagementStartDate ManagementEndDate Software Client 4331 3817 13995 2/26/2014 4/28/2016 YAR-RIV HIL-COM-LLC 4331 5815 13995 4/29/2016 10/25/2016 YAR-GS HIL-COM-LLC 17 5342 13638 1/27/2016 7/16/2016 ONE-GS WOO-INV- 17 6086 13638 7/17/2016 7/24/2019 ONE-GS WOO-INV- 17 9603 13638 7/25/2019 ONE-GS EAT-VAN-MAN 2388 8319 15474 6/1/2018 5/2/2022 ONE-GS CAM-DEV-GRO 2388 14888 15474 11/1/2023 ONE-GS CAM-DEV-GRO 10 2760 11507 6/17/2013 7/16/2016 ONE-GS PGI-- 10 6083 11507 7/17/2016 12/29/2020 ONE-GS PGI-- 32 5862 14032 6/15/2016 11/14/2018 ONE-GS BON-- 32 8845 14032 11/15/2018 7/12/2023 ONE-GS MOM-REA-EST 37 9437 16255 5/29/2019 11/4/2021 ENT-GS GRE-INV-GRO 37 12461 16255 11/5/2021 ENT-GS GRE-INV-GRO 39 4896 13633 8/15/2015 10/22/2016 ONE-GS BLU-- 39 5314 13633 12/11/2015 10/22/2016 ONE-GS BLU-- 39 6485 13633 10/23/2016 7/31/2018 ONE-GS BLU-- 41 11550 18004 1/19/2021 9/30/2022 ONE-GS DWS-- 41 14016 18004 10/1/2022 ONE-GS DWS-- 49 2723 11484 5/13/2013 11/27/2016 ONE-GS CHI-KLE-PRO 49 6682 11484 11/28/2016 ONE-GS CHI-KLE-PRO 67 5285 13595 1/23/2016 2/20/2017 ONE-GS STO-- 67 7063 13595 2/21/2017 12/21/2017 ONE-GS STO-- 70 2824 11555 9/19/2013 12/10/2013 ONE-GS GE-- 70 2935 11555 12/11/2013 1/18/2017 ONE-GS BLA-- 70 6922 11555 1/19/2017 7/23/2018 ONE-GS BLA-- 76 4361 12924 12/29/2014 10/17/2016 ONE-GS CAM-DEV- 76 6476 12924 10/18/2016 1/31/2017 ONE-GS CAM-DEV- 76 7009 12924 2/1/2017 ONE-GS USA-REA-EST 95 2089 11314 11/19/2012 12/7/2015 ONE-GS GRE-INV-GRO 95 5255 11314 12/8/2015 1/18/2017 ONE-GS BLA-- 95 6920 11314 1/19/2017 6/15/2017 ONE-GS BLA-- 110 1883 11166 2/24/2012 7/11/2012 ONE-GS C-III-ASS 110 2023 11166 7/12/2012 6/25/2014 ONE-GS C-III-ASS 119 200 7591 10/10/2008 9/30/2012 ONE-GS DWS-- 119 2079 7591 10/1/2012 3/16/2016 ONE-GS DWS-- 133 8103 15318 2/23/2018 6/30/2018 ONE-GS INV-- 133 8104 15318 2/23/2018 6/30/2018 ONE-GS INV-- 136 4389 12938 12/19/2014 8/30/2016 ONE-GS BC-PRO- 136 6289 12938 8/31/2016 10/11/2018 ONE-GS BC-PRO- 145 1539 10959 7/19/2011 11/2/2012 ONE-GS LNR-PRO- 145 2172 10959 11/3/2012 11/12/2014 ONE-GS LNR-PRO- 154 1138 10713 8/12/2010 4/30/2013 ONE-GOL ARC-- 154 2701 10713 5/1/2013 8/30/2016 ONE-GS BC-PRO- 154 6279 10713 8/31/2016 10/11/2018 ONE-GS BC-PRO- 155 2764 11510 7/2/2013 8/21/2016 ONE-GS AEW-- 155 6182 11510 8/22/2016 6/18/2019 ONE-GS AEW-- 163 9693 16456 9/1/2019 6/13/2021 ONE-GS SLA-REA-EST 163 11988 16456 6/14/2021 10/25/2021 ONE-GS ILA-INV- 169 2945 11637 12/20/2013 10/26/2016 ONE-GS HAT-CRE-PAR 169 6531 11637 10/27/2016 7/31/2017 ONE-GS HAT-CRE-PAR 187 5362 13659 1/9/2016 5/6/2018 ONE-GS BEN-GRE- 187 8244 13659 5/7/2018 ONE-GS EAT-VAN-MAN 190 3084 11726 4/16/2014 12/12/2014 ONE-GS KAN-REA-COR 190 4363 11726 12/13/2014 10/12/2016 ONE-GS JP-MOR- 205 1644 10991 8/1/2011 12/11/2013 ONE-GS DRA-- 205 2931 10991 12/12/2013 11/30/2016 ONE-GS DRA-REA-INV 210 1123 10698 8/9/2010 10/22/2013 YAR-TRI TRI-REA-EST 210 2887 10698 10/23/2013 5/14/2015 YAR-TRI TRI-REA-EST - lbendlin
See attached for something to get you started. It has both the amortization logic (spelling out the months) and a custom visual that can show date ranges.
