New Column with Total Sales

[Allan77R2V1]

I can't seem to figure this out and looking for some guidance. I have the following table

Date	Sales
October 2017	$600
October 2017	$300
October 2017	$1,000
September 2017	$600
September 2017	$500
September 2017	$1,300
August 2017	$2,200
August 2017	$250

I want to add a 3rd column that adds the monthly sales. Any suggestions?

Date	Sales	Monthly Total
October 2017	$600	$1,900
October 2017	$300
October 2017	$1,000
September 2017	$600	$2,400
September 2017	$500
September 2017	$1,300
August 2017	$2,200	$2,450
August 2017	$250

[Ashish_Mathur]

Hi,

If your objective is to see the Monthly Total in a visual, just drag the Date column to your visual and write a simple SUM measure to add the numbers in the Sales column.  Why do you need a Monthly Total column in your base data table?

[Allan77R2V1]

Hi Ashish,

I didnt provide my end goal as I thought by getting the answer would point me in the right direction.  So here is my end goal. I also have another table similar to the on below. It also only has the first two columns. My goal is to calculate the the difference of estimated and actual sales by month and then in the visualizer graph the difference by month.

Date	Estimate Sales	Estimated Monthly Total
October 2017	$900	$2,100
October 2017	$400
October 2017	$800
September 2017	$200	$2,700
September 2017	$1,000
September 2017	$1,500
August 2017	$1,800	$2,300
August 2017	$500

[Ashish_Mathur]

Hi,

To accomplish that, we do not need to do what you originally asked for.  Share both datasets and show the expected result.

[Allan77R2V1]

Below are samples of the date and the desired outcome. Table 1 is the estimated sales and operations. It will have one line per month for each item. I need to some how indentify the sales row for each month.

The second table will have several lines per monthn and the number of lines per month can vary. I need to add each month up to get my actual sales

The last table is the desired out come. It has on line per month and it has subtracted the monthly estimated sales from the actual sales

Table 1
Estimated Sales and Operations
October 2017	Sales	$2,600
October 2017	Operations	$500
September 2017	Sales	$3,100
September 2017	Operations	$750
August 2017	Sales	$1,000
August 2017	Operations	$300
Table 2
Actual Sales
October 2017	1900
October 2017	1200
September 2017	1000
September 2017	900
August 2017	2000
August 2017	1500
Sales Variance (Desired Outcome)
October 2017	$                               500
September 2017	$                         (1,200)
August 2017	$                           2,500

[Ashish_Mathur · Accepted solution]

Hi @Allan77R2V1,

You may refer to my solution here.

Hope this helps.

[Allan77R2V1]

Many thanks for your help. This worked perfectly.

[Ashish_Mathur]

You are welcome.

[Zubair_Muhammad]

@Allan77R2V1

Check this file

Following formula is used for the New / desired table

NewTable =
ADDCOLUMNS (
    SUMMARIZE ( Table2, Table2[Month] ),
    "Difference", CALCULATE ( SUM ( Table2[Amount] ) )
        - CALCULATE (
            SUM ( Table1[Amount] ),
            FILTER ( Table1, Table1[Month] = Table2[Month] && Table1[Type] = "Sales" )
        )
)

[Zubair_Muhammad]

Hi @Allan77R2V1

Go to Modelling Tab, press the NEW TABLE button and enter this formula

New Table =
ADDCOLUMNS (
    SUMMARIZE ( TableName, TableName[Date], TableName[Sales] ),
    "Monthly Total", IF (
        TableName[Sales]
            = CALCULATE (
                FIRSTNONBLANK ( TableName[Sales], TableName[Sales] ),
                FILTER ( ALL ( TableName ), TableName[Date] = EARLIER ( TableName[Date] ) )
            ),
        CALCULATE (
            SUM ( TableName[Sales] ),
            FILTER ( ALL ( TableName ), TableName[Date] = EARLIER ( TableName[Date] ) )
        )
    )
)