Calculating Percentage time remaining

[ccarpent]

Hi

I am trying to create a Measure to work out a percentage of time remaining on a project, this takes into account todays date.  All the data in in a single table.  The table has many rows, the difference is they have a varied budgets amounts but they all share the same 'Code', and same 'Start' and 'End' date.  I can created a calculated columns hoping this would help but my calculation just throughs out odd amounts; I am sure its becase there is the same value for each code per row.  I cant groupby the table, this is my sample:

Sample Data

So I was aiming to create ameasure that when I select the code 'ABC' in a matrix or pivot, I just get "-1%", but it does some thing alltogether and seems to add up numerous values.  

I have tried formula like: SUMX('TableA', sum('TableA'[Remaining Days')/sum('TableA'[Project No Of Days]))

I alos attempted a separte measure for calculatign the "no of days" rather than a calculated column, but again odd results, sample forulmula like this: SUMX('TbaleA', sum('TableA'[End Date')-SUM('TableA'[Start Date]))

Is it something to do with 'Row Context' maybe?  There is no filter contect, that I know of.   What am I doing wrong, in theory its a simple calculation?

Chris

[amitchandak]

@ccarpent , Try like

avergageX(summarize(Table, Table[code],Table[StartDate],Table[endDate],"_1", max(Table[project no of Days]), "_2",max(Table[remaining Days])), divide([_2],[_1]))

[Fowmy]

@ccarpent 

What is your expected results ? Can you show it for  one code?

Can you try this :

m = 

SUMX('TableA', 
DIVIDE(
    'TableA'[Remaining Days],
    'TableA'[Project No of days])
)

 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[ccarpent]

Hi Fowmy

This is the results I get:

code	Start Date	End date	Project No of days	This is what it should be  ::Remaining Days	The resulst I want ::Time remaining %	New Measure
ABC	05/09/2016	04/08/2020	1429	-20	-1%	-5%
ZZZ	01/05/2008	31/12/2008	244	-4254	-1743%	-15752%
AAA	01/12/2019	30/11/2020	365	98	27%	27%
EFG	01/07/2018	30/12/2020	913	128	14%	127%

The colums in Green is what I am tryign to acheive.

The column in Blue is what I am am trying to acheive.

The column in Red is the new measure, its getting there slowly.

I made sure all the values come from the same table, trying to remove an filter context.  I may need to rethink my how I am doing calculations, these are based around todays date.

Alos even though not inlustrated, onthe source tabel theerare several rows for ABC, ZZ etc, here I am just showing one line for simplicity.

Project No of Days = start date-end date

Remaining Days = End Date - Today()

% Time Remaining Measure = Remaining Days / Project No of Days

Chris

[Fowmy · Accepted solution]

@ccarpent 

You need two measures:

Remaining Days = 

INT(TODAY() - MAX(Project[End date]))

% Remaining = 

DIVIDE(
    [Remaining Days],
    MAX(Project[Project No of days])
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[ccarpent]

Fowmy

Looking better thanks.  I also need to calculate a % percentage through the budget but I will use the examples posted here to work that part out.

Cheers Again.

Chris