cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
loisskinner
Frequent Visitor

DAX formulae for overdue percentages

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)

2 REPLIES 2
Microsoft Eric_Zhang
Microsoft

Re: DAX formulae for overdue percentages

@loisskinner

Could you post any sample data and expected output?

loisskinner
Frequent Visitor

Re: DAX formulae for overdue percentages

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 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors