Forum Discussion
Calculating Percentage time remaining
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
Anonymous
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 🙂
5 Replies
- Fowmy
Super User
Anonymous
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 🙂
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
Anonymous
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 🙂
- amitchandak
Anonymous , 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]))
