I am attempting to model recovery of outstanding debts based on a number of factors:
- projected recovery amount of each debt type
- projected payment timeframe of each debtor
- start date of the debt recovery project
- the ranking order in which the recovery of debts should be actioned
- what if parameters for:
- Recovery Cost per hour
- Average Hrs Effort per debt
- Number of staff allocated to the debt recovery project.
I am able to generate the desired visual with my test record set which contains 100 records.
However when attempting to use my actual record set of > 100K records, the visual exceeds the available resources.
I would really appreciate some guidance on how to improve my DAX measures for faster performance.
It is my understanding that I cannot leverage calculated columns due to the use of what if parameters in the model.
Below are the DAX measures upon which the Cumulative Recovery by Date Worked measure in the line chart visual relies. I have also attached the pbix file.
Many thanks in advance
Total Paid = CALCULATE( SUM( DimWorkAttribute_A[Median Paid] ), TREATAS( VALUES(FactWork[WorkTypeKey] ), DimWorkAttribute_A[WorkTypeKey] ) ) Recovery Cost Per Work Unit = IF( ISBLANK( [Total Paid] ), BLANK(), [_Average Hours Effort Per Work Unit Value] * [_Recovery Cost Per Hr Value] ) Projected Recovery = [Total Paid] - [Recovery Cost Per Work Unit] Total Hrs Effort Per Work Day = VAR FTEHrsPerDay = 8 VAR AllocatedFTEResources = [_Number of Team Members Value] VAR TotalEffortPerDay = FTEHrsPerDay * AllocatedFTEResources RETURN TotalEffortPerDay Work Item Number Of Work Days From Start = VAR WorkEffort = [_Average Hours Effort Per Work Unit Value] VAR HoursPerDay = [Total Hrs Effort Per Work Day] VAR WorkDayNumber = ROUNDUP( MAXX( DimWorkRank, DIVIDE( DimWorkRank[WorkRank] * WorkEffort, HoursPerDay ) ), 0 ) RETURN WorkDayNumber Cumulative Work Days = VAR CurrentWorkDate = MAX( DimDate[Date] ) VAR DateFilter = FILTER ( ALLSELECTED( DimDate ), DimDate[Date] <= CurrentWorkDate ) RETURN CALCULATE( SUM( DimDate[IsWorkDay] ), DateFilter ) Projected Date to Start Work Unit = VAR ProjectDates = ALLSELECTED( DimDate ) VAR ProjectedWorkStartDate = CALCULATE( MIN( DimDate[Date] ), FILTER( ProjectDates, [Cumulative Work Days] = [Work Item Number Of Work Days From Start] ) ) RETURN IF( ISBLANK( [Work Item Number Of Work Days From Start] ), BLANK(), ProjectedWorkStartDate ) Projected Recovery by Date Worked = VAR WorkDate = SUMMARIZE( DimWorkRank, DimWorkRank[WorkRank], "ProjectedRecovery", [Projected Recovery], "WorkDate", [Projected Date to Start Work Unit] ) VAR DateTable = SUMMARIZE( DimDate, DimDate[Date], "WorkDate", MAX( DimDate[Date] ) ) VAR SummaryTable = NATURALINNERJOIN( DateTable, WorkDate ) RETURN SUMX( SummaryTable, [ProjectedRecovery] ) Cumulative Projected Recovery by Date Worked = VAR CurrentDate = MAX( DimDate[Date] ) VAR DateFilter = FILTER( ALLSELECTED(DimDate), DimDate[Date] <= CurrentDate ) RETURN IF( ISBLANK( [Projected Recovery by Date Worked] ), BLANK(), CALCULATE( [Projected Recovery by Date Worked], DateFilter ) )
Dynamic Timeframe Debt Recovery.pbix (133.3 KB)