cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
loisskinner Frequent Visitor
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
Moderator Eric_Zhang
Moderator

Re: DAX formulae for overdue percentages

@loisskinner

Could you post any sample data and expected output?

loisskinner Frequent Visitor
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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors