Computing SUM on CalculateTable from Union of multiple tables

[jnickell]

First thanks for the community because I wouldn't have gotten as far as I  have withouth lots of helpful ideas here.  

My task is to produce a Stacked column chart that reflects costs we have either already incurred for the year, costs we have "budgeted" to incur this year or costs we have forecasted for the year.  In the logic there is a difference between budgeted and forecasted.   Budgeted being the original sales budget for the work and a single point in time. Forecasted are the cost spread over the months expected to incur them.  

I have gotten about 98% of the way and I'm able to show "real costs", "forecasted costs" and "budgeted costs" for the period.  What has got me stumped is the request to reduce the current year budget costs by costs in prior years.  
This could happen if work was sold in previous year, never forecasted, expected to complete this year, but costs were incurred last year which reduce the "real" value of the contract in the present year.  (note, this situation does not 'happen often', but I'm trying to address it just the same)

My Dataset at this point includes a master Job list table, and a Job cost table, and a "inverted Job cost table" referencing the original Job cost table.  

What I thought that I could do was reference the original job cost table and 'invert' all the amounts for its entries (*-1).  Then Union this to the Calculated Table I'm using in my measure to produce the stacked column chart.  

Unfortunately, this is not working.  I believe I've got the query setup correctly, but the measure returns 0, where it should return a value.  Disclaimer that I'm "learning through doing".

I'd appreciate your pointers on how to correct the measure.  

_m_Period Costs = 
    // currently trying to refine/determine why the active, unbudgeted job is not being captured for totals.
    VAR CurrentYearBegin = DATE(YEAR(TODAY()),1,1)
    VAR CurrentMonth = DATE(YEAR(TODAY()),MONTH(TODAY()),1)
    VAR CurrEstMonthBeg = 
        IF ([Current Projection Month] < CurrentMonth, 
            CurrentMonth, [Current Projection Month])
    
    // Workaround so could get Budgets from prior years (operational or Baseline)
    VAR JCTable = CALCULATETABLE(JobCosts,
                        CROSSFILTER(JobCosts[Date], Dates[Date],None), JobCosts[Current] = TRUE(), FullJobInfo[ActiveFL] = TRUE())

    RETURN
        Sumx(
            CALCULATETABLE(
                Union(
                    // Pull in Applied Cost for Inactive Jobs
                    Filter(JobCosts, 
                        SELECTEDVALUE(FullJobInfo[ActiveFL]) = FALSE() && JobCosts[Description] = "Applied"),
                    // Applied Costs for Active & Forecasted
                    FILTER(JobCosts,
                        JobCosts[Date] < CurrEstMonthBeg && JobCosts[Description] = "Applied" && [Costing Basis] = "Forecasted" &&
                            SELECTEDVALUE(FullJobInfo[ActiveFL]) = TRUE()),
                    // Forecasted Costs for Active & Forecasted
                    FILTER(JobCosts,
                        JobCosts[Date] >= CurrEstMonthBeg && JobCosts[Description] = "Forecast" && JobCosts[Current] = TRUE() 
                            ), --&& SELECTEDVALUE(FullJobInfo[ActiveFL]) = TRUE()),
                    // Active and Non-Forecasted: Budgeted Values (Budgets are 0,1 currently)
                    FILTER(JCTable,
                        [Costing Basis] = "Budgeted" && [CRM Current Year Complete] 
                            && JobCosts[BudgetLevel] < 2),
                    // Active and Non-Forecasted: Costs from prior year, to subtract from overall budget value
                    FILTER(JCTable,
                        [Costing Basis] = "Budgeted" && [CRM Current Year Complete] 
                            && JobCosts[Description] = "InvApplied" && JobCosts[Date] < CurrentYearBegin),
                    // Period costs for Active Non-Forecasted Jobs when not scheduled to complete in this year
                    FILTER(JobCosts,
                        [Costing Basis] = "Budgeted" && NOT([CRM Current Year Complete]) && JobCosts[Description] = "Applied" &&
                            SELECTEDVALUE(FullJobInfo[ActiveFL]) = True())
                ),  // End Union
            JobCosts[Amount]),  // End CalculateTable
        [Amount]) // End Sumx))

Here's a screenshot of "not working". The applied column should have a negative value of ~$5000 for 178, instead it's 0.

I'm not sure of the best way to provide a dataset for assistance, since it is financial info.

EDIT: 17 Apr 2019::

I was able to get a part of the way around the issue, by appending the "invertedAppliedCosts to my Job Cost table.  This is getting me almost the entire way to where I need to be.  

The problem I'm running into now still revolves around the same job, but this time is "only" in the totals column.  

My totals are not including value that this one job would contribute.  The "by line" numbers are correct it's just the totals.  

I've already leveraged @Greg_Deckler 's very helpful post on the topic.  The original dataset has been updated PBI Dataset

I've update the measure code that I am using for the "correct by line" results.

[v-shex-msft]

Hi @jnickell ,

Please share some sample data with your formula to test, your formula seems complex and hard to direct find out calculation logic on total level.

How to Get Your Question Answered Quickly

Measure Totals, The Final Word

Regards,
Xiaoxin Sheng

[jnickell]

@v-shex-msft I've uploaded a dataset and included some progress that I was able to make yesterday.   Thank you for any pointers you can provide. 

[jnickell]

I think that I finally found at least one way to address my issue, however I'm not sure that it's the best method as in my head it seems very expensive.  

The issue that I seem to have is the fact that I want a particular part of the measure to not be bound by filters applied to the report.  

What I'm trying to get to is the costs we have or expect to have for the company for the year.  However in a specific use case the costs that were incurred in the prior year(s) play a role in computing the cost for the present year.  

I'm using the report filter to constrain the measure to the current year, so I have to compose the measure to over-ride the filter on the page.  

I managed to compose the first measure below that returns a "row by row" accurate result.  Using @Greg_Deckler 's technique for composing a second 'sumx' measure that summarizes the source table using the 'row by row' measure.  To get an accurate total I also had to summarize the source table without the date filter. (second measure).  This causes the measure to take a long time to load as there are 1 million+ records in the Costs table.  

Am I going about this incorrectly for my desired goal of being able to visualize not just the total expected cost for the period, but what types of costs make up that number. The point is being able to distinguish between real costs that count toward revenue and 'projected/expected costs' that could still slip.

Edit, I realized I forgot to include the measures I referenced:

_m_Period Costs = 
    // currently trying to refine/determine why the active, unbudgeted job is not being captured for totals.
    VAR CurrentYearBegin = DATE(YEAR(TODAY()),1,1)
    VAR CurrentMonth = DATE(YEAR(TODAY()),MONTH(TODAY()),1)
    VAR CurrEstMonthBeg = 
        IF ([Current Projection Month] < CurrentMonth, 
            CurrentMonth, [Current Projection Month])
    
    // Workaround so could get Budgets from prior years (operational or Baseline)
    VAR JCTable = CALCULATETABLE(JobCosts,
                        CROSSFILTER(JobCosts[Date], Dates[Date],None), JobCosts[Current] = TRUE(), FullJobInfo[Active] = TRUE(), FullJobInfo[Revenue Generating] = TRUE(),
                        USERELATIONSHIP(FullJobInfo[Job Number], JobCosts[ProjectNum])
                        )
    
    RETURN
        Sumx(
            CALCULATETABLE(
                Union(
                    // Pull in Applied Cost for Inactive Jobs
                    Filter(JobCosts, 
                        SELECTEDVALUE(FullJobInfo[Active]) = FALSE() && SELECTEDVALUE(FullJobInfo[Revenue Generating]) && JobCosts[Description] = "Applied" ),
                    // Applied Costs for Active & Forecasted
                    FILTER(JobCosts,
                        JobCosts[Date] < CurrEstMonthBeg && JobCosts[Description] = "Applied" && [Costing Basis] = "Forecasted" &&
                            SELECTEDVALUE(FullJobInfo[Active]) = TRUE() && SELECTEDVALUE(FullJobInfo[Revenue Generating])),
                    // Forecasted Costs for Active & Forecasted
                    FILTER(JobCosts,
                        JobCosts[Date] >= CurrEstMonthBeg && JobCosts[Description] = "Forecast" && JobCosts[Current] = TRUE() 
                            && SELECTEDVALUE(FullJobInfo[Revenue Generating])),
                    // Active and Non-Forecasted: Budgeted Values (Budgets are 0,1 currently)
                    FILTER(JCTable,
                        [Costing Basis] = "Budgeted" && [CRM Current Year Complete] 
                            && JobCosts[BudgetLevel] < 2 && JobCosts[Current] = TRUE()),
                    // Active and Non-Forecasted: Costs from prior year, to subtract from overall budget value
                    FILTER(JCTable,
                        [Costing Basis] = "Budgeted" && [CRM Current Year Complete] 
                            && JobCosts[Description] = "InvApplied" && JobCosts[Date] < CurrentYearBegin ),
                    // Period costs for Active Non-Forecasted Jobs when not scheduled to complete in this year
                    FILTER(JobCosts,
                        [Costing Basis] = "Budgeted" && NOT([CRM Current Year Complete]) && JobCosts[Description] = "Applied" &&
                            SELECTEDVALUE(FullJobInfo[Revenue Generating]) && SELECTEDVALUE(FullJobInfo[Active]) = True())
                ),  // End Union
            JobCosts[Amount]),  // End CalculateTable
        [Amount]) // End Sumx))))

"The Accurate totals measure

Costs for Period By Job = 
    VAR __table = SUMMARIZE(
            CALCULATETABLE(JobCosts, CROSSFILTER(JobCosts[Date], Dates[Date], None ))
            ,JobCosts[ProjectNum], "__value",[_m_Period Costs])
    RETURN
        If(HASONEVALUE(JobCosts[ProjectNum]),
            [_m_Period Costs],
            SUMX(__table,[__value]))

[v-shex-msft]

Hi @jnickell ,

I think this should relate to your relationship direction, you can take a look at following blog to know more about this:

What is the Direction of Relationship in Power BI?

Please understand that this link is provided with no warranties or guarantees of content changes, and confers no rights.

Regards,

Xiaoxin Sheng

[jnickell]

Thanks, I'm going to work on getting the dataset today.  I couldn't find my post over the weekend and thought it has gottent deleted.  Thanks for replying so I know it didn't go into the ether.

[jnickell]

Here's the dataset file. I've tried to clean it up to the 'required' tables and fields.  PBI dataset

If there's a better way to share the file, please provide direction.  I don't see how I can attach directly to the forum.  

In my original post, I revised the name of the measure in the snippet.  

Period Cost 6  is the name of the measure that I'm trying to work on currently (others are previous versions as I've worked through the different costs groups)

Thank you for any assistance!