topic Re: Calculating Percentage time remaining in DAX Commands and Tips

Calculating Percentage time remaining

Anonymous — Mon, 24 Aug 2020 14:37:54 GMT

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:

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

Re: Calculating Percentage time remaining

Fowmy — Mon, 24 Aug 2020 14:55:16 GMT

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 🙂

YouTube, LinkedIn

Re: Calculating Percentage time remaining

amitchandak — Mon, 24 Aug 2020 15:26:00 GMT

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]))

Re: Calculating Percentage time remaining

Anonymous — Mon, 24 Aug 2020 19:36:03 GMT

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

Re: Calculating Percentage time remaining

Fowmy — Mon, 24 Aug 2020 20:16:42 GMT

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 🙂

YouTube, LinkedIn

Re: Calculating Percentage time remaining

Anonymous — Tue, 25 Aug 2020 21:25:44 GMT

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