DAX formulae for overdue percentages

[loisskinner]

Thank you in advance for any help that is offered.

I have data that consists of around 50,000 rows of dates tasks and due date.

I have a count of current tasks by contract, tasks for current and previous month, an overdue count and a 3month overdue count.

I also have an overdue percentage of task count formluae and the same for 3 months overdue.

I am struggling to get a formula that will give me the overdue percentage of the current month tasks. As in which tasks that have been logged this month are overdue. Similar for the previous month and for 3 months overdue.

OR a formulae that will allow me to calculate the number of tasks overdue in one month? would i need extra date tables for this.

All information is taken from an uploaded excel spreadsheet.

Please see my formulae below. Please do not hesitate to get in touch if you require any further information.

Overdue = if(Calculate(counta(PPMData[TaskPrefix]),FIlter(ALL(PPMData),PPMData[TaskPrefix]=PPM_CALCS[Contract Prefix]),PPMData[Overdue]=1)<>0,Calculate(counta(PPMData[TaskPrefix]),FIlter(ALL(PPMData),PPMData[TaskPrefix]=PPM_CALCS[Contract Prefix]),PPMData[Overdue]=1),0)

3MOverdue = if(Calculate(counta(PPMData[TaskPrefix]),FIlter(ALL(PPMData),PPMData[TaskPrefix]=PPM_CALCS[Contract Prefix]),PPMData[3M Overdue]=1)<>0,Calculate(counta(PPMData[TaskPrefix]),FIlter(ALL(PPMData),PPMData[TaskPrefix]=PPM_CALCS[Contract Prefix]),PPMData[3M Overdue]=1),0)

%Overdue = if(DIVIDE(PPM_CALCS[Overdue],PPM_CALCS[CountLogTasks])<>0,DIVIDE(PPM_CALCS[Overdue],PPM_CALCS[CountLogTasks]),0)

%3MOverdue = if(DIVIDE(PPM_CALCS[3MOverdue],PPM_CALCS[CountLogTasks])<>0,DIVIDE(PPM_CALCS[3MOverdue],PPM_CALCS[CountLogTasks]),0)

CurrentMonthCount = if(Calculate(counta(PPMData[TaskPrefix]),FIlter(ALL(PPMData),PPMData[TaskPrefix]=PPM_CALCS[Contract Prefix]),PPMData[CurrentMonth]="Current")<>0,Calculate(counta(PPMData[TaskPrefix]),FIlter(ALL(PPMData),PPMData[TaskPrefix]=PPM_CALCS[Contract Prefix]),PPMData[CurrentMonth]="Current"),0)

[Eric_Zhang]

@loisskinner

Could you post any sample data and expected output?

[loisskinner]

Under the data protection act i wouldnt be able to share our business information on this thread.

origianlly the data was on excel, presented as contract and month and the percentage overdue contribution to the total tasks.

In power bi this is currently diplayed as a breakdown of percentage contribution to total tasks rasied, group, region, contract.

In excel we have the data presented monthly. This displays what percentage of logged tasks are overdue.

Ideally I would like the data presented in the same way. But currently it is presented in Power BI by contract name and not month.

Alternatively the data could be presented as % of current month logged tasks that are overdue, shown by group, region and contract.

I hope this helps.

Kindest regards