Remaining fee total of measure and date issues

[dafrey23]

I've got a couple problems with a model of mine.  The first issue is that I'm trying to calculate the total of a measure which is calculating the remaining fee (aka backlog) for a given project.  That measure is taking the contractual fee and subtracting a measure that calculates the running total of all transactions against that contract to arrive at remaining fee.  If the remaining fee drops below zero (aka over budget), then the remaining fee is zero, not a negative number.  My measures work fine on an individual project basis, but I need to sum up all projects to get a total remaining fee across the company, which doesn't work.

The second issue I don't understand is that when I drill down below the monthly level to the day level I seem to lose data.  In the end I want to chart this on a week to week basis across at least the last 52 weeks so this is going to be an issue.

The data is in three tables.  The first table (PRSimple) contains the project number (WBS1), project fee (FeeDirLab), and the project creation date (CreateDate).  The second table (LDSimple) contains the project number (WBS1), transaction value (BillExt), and transaction date (TransDate).  The third table is a date table (DateTable).

There is a date relationship between the date table and the transaction date in LDSimple.  There's a project number relationship between PRSimple and LDSimple using WBS1.

Here's a link to download a sample model with similar but fake data...

I've reviewed many different methods for a total of a measure including the GregDeckler blog article, but something isn't working.  The measure currently in the model is the simple version that works at the project level and doesn't include the HASONEVALUE trick or anything else.  Any help is appreciated.

[Bmejia · Accepted solution]

Sorry tried to reply sooner I believe this should help you out if you not already resolved this issue.  Is off by one dollar cause I believe is the actual sum. 

Create a measure like this I am reusing all your current measures.

NewBacklog =

IF(HASONEVALUE(PRSimple[FeeDirLab]),[Backlog], --Calculates what is already correct

SUMX(PRSimple, -- Calculates the Total value

    IF(CALCULATE(SUM(PRSimple[FeeDirLab]))-[TotalBilling]>0,

        CALCULATE(SUM(PRSimple[FeeDirLab]))-[TotalBilling],0)))

[Bmejia · Accepted solution]

Sorry tried to reply sooner I believe this should help you out if you not already resolved this issue.  Is off by one dollar cause I believe is the actual sum. 

Create a measure like this I am reusing all your current measures.

NewBacklog =

IF(HASONEVALUE(PRSimple[FeeDirLab]),[Backlog], --Calculates what is already correct

SUMX(PRSimple, -- Calculates the Total value

    IF(CALCULATE(SUM(PRSimple[FeeDirLab]))-[TotalBilling]>0,

        CALCULATE(SUM(PRSimple[FeeDirLab]))-[TotalBilling],0)))

[dafrey23]

This seems to work perfectly.  Now I need to study up on why, but that's my problem.

Thanks so much.

[Bmejia]

dafrey23,

It would be helpful if you provide a sample visual of what is wrong and what values you are expecting to see where you see the wrong data.  I think right now we all looking at your backlog measure and it seem correct as you mention, but we don't know where is wrong or what the expected output is.

Thanks,

[dafrey23]

Certainly.  The problem is the totals are summing up all the fees and subtracting the sum of all the transactions.  However on any individual project if the sum of the transactions is greater than the fee, then the value should zero, not a negative number.  So the totals should be summing up all the non-negative numbers. This behavior is indicated when the total at the bottom is less than the sum of the individual projects.

[Anonymous · Accepted solution]

Hi, @dafrey23 

You can try the following methods. 

Measure:

Measure = 
Var _table=SUMMARIZE(PRSimple,PRSimple[WBS1],"Value",[Backlog])
RETURN
SUMX(_table,[Value])

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dafrey23]

This also works.  Thanks.