Taking the SUM between dates with blanks

[Anonymous]

This is the first time I've posted on here so do let me know how I can make this post better. I am trying to take a sum between dates with blanks in between and the blank spacing is changing as well. 

Okay so the Date column is acting as an index and the JobsDate is the specific date that occurs in another table. So that's just pulling the dates that actually have data assigned to them. 

The Charged column is just taking the Sum of another column in the other table where the dates = JobsDate.

Now, the Cost column is coming from a third table. What I want is take the sum for that third table column where all the dates assigned to these values are <= JobsDate. I have this DAX which works for the first but then starts to add on top of the last.

Cost = 
CALCULATE(
    SUM('1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 SupplierInvoices'[AmountExcVAT]),
    FILTER(
        ALL('1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 SupplierInvoices'),
        '1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 SupplierInvoices'[Date] <= 'TotalSupplierSpendTable'[JobsDate]
    )
)

where 'TotalSupplierSpendTable'[JobsDate] is the name of the table in the screenshot and '1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 SupplierInvoices' is the third table I mentioned.

I have manually checked that the value of £9614.52 is correct. The second value of £11694.63 = £9614.52 + x. So the second value should actually be £2080.11

How can I take the sum between each Non Blank value of JobsDate? Or even some how take the difference between each Non Blank value of Cost?

I hope someone can give some light to this problem

Thanks

[v-xuding-msft]

Hi @Anonymous ,

For your data model, are the columns from 4 tables? Date column is in the table1. JobDate is from TotalSupplierSpendTable. Charged is from table2. And the Cost is in the table '1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 SupplierInvoices'.  Do I understand correctly?  And how do you get £2080.11?

Can you please share a dummy file that we will understand clearly? 

Best Regards,

Xue Ding

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

[Anonymous]

Oh dear, my apologies for how confusing this problem has become. I will work to make it as clear as possible.

Table 1 (CostofGoodsSoldTable): This is the calculated table I have made with the following operation:

Table1 = 
    ADDCOLUMNS(
        CALENDAR(MIN('1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 SupplierInvoices'[Date]),MAX('1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 Jobs'[InvoiceDate])),
        "Month" ,  DATE( 
                     YEAR([Date]), 
                     MONTH([Date]),
                     1
                     )		
    )

This creates a date index running from 02/06/2016 to 26/09/2019.

Table 2 (1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 Jobs): This table has records of jobs and their dates. Each job has a Materials Charged and a Materials Cost. So what I have done is the following operation to add a calculated column to Table 1. This kind of "pick out" the job dates from the full index, like so:

JobsDate = 
CALCULATE(
    FIRSTNONBLANK('1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 Jobs'[InvoiceDate], 1),
    FILTER( '1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 Jobs',
        '1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 Jobs'[InvoiceDate] = 'CostofGoodsSoldTable'[Date]
    )
)

Then I added 3 more calculated columns, the first 2 which sum all the Charged and Cost values for each job date (job dates are not unique), and a third which finds the profit, like so:

Charged = 
CALCULATE(
    SUM('1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 Jobs'[MaterialsCharged]),
    FILTER(
        ALL('1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 Jobs'),
        '1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 Jobs'[InvoiceDate] = 'CostofGoodsSoldTable'[Date]
    )
)

Cost = 
CALCULATE(
    SUM('1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 Jobs'[MaterialsCost]),
    FILTER(
        ALL('1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 Jobs'),
        '1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 Jobs'[InvoiceDate] = 'CostofGoodsSoldTable'[Date]
    )
)

Profit = [Charged]-[Cost]

Table 3 (1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 SupplierInvoices😞 This table records the Supplier Invoices which correlate with Table 2 as the Cost.

Table 4 (TotalSupplierSpend): This table I created to be the same as Table 1 up to the Charged column. I then create a calculated column and take the sum of the Cost for each date in Table 3 (supplier dates are not unique), like so:

Cost = 
CALCULATE(
    SUM('1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 SupplierInvoices'[AmountExcVAT]),
    FILTER(
        ALL('1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 SupplierInvoices'),
        '1B825BE6-57C1-4091-AA6C-11CBCCB2EEE4 SupplierInvoices'[Date] = 'TotalSupplierSpendTable'[Date]
    )
)

 This is where the key issue lies. The dates from Table 3 do not line up with dates from Table 2.

For Example, near the start of the date index, several dates from Table 3 occur before the first date from Table 2 occurs. I will attach a picture of this:

This means that, for each JobsDate row that is not blank, I can't calculate the profit.

So what I would like to do is somehow take the sum upto the JobsDate and then have that value in the Cost row. I have been able to do that for the first occurance of JobsDates but then I need to sum from 0 up until the next JobsDate and then have that value in the next Cost row. What I have so far is a Cumulative Sum so that's why the first value is right but then the second value is the sum of the first JobsDate and the second JobsDate. 

I would like to find a way to create a Cumulative Sum that can somehow "reset" after each JobsDate. Does any of this make sense? I don't know how to include a dummy file as you mentioned before.