How to calculate monthly totals?

[anandav]

Hi All,

I have the following data

Country   Product        Sales     Qty        Sales Date 

A	Product 1	100	10	2/01/2018
B	Product 2	200	20	2/01/2018
C	Product 3	300	30	3/01/2018
D	Product 1	400	40	5/01/2018
E	Product 2	500	50	7/02/2018
A	Product 1	600	60	10/02/2018
B	Product 2	700	70	11/02/2018
C	Product 2	800	80	12/03/2018
D	Product 2	850	90	14/03/2018
D	Product 3	1000	100	15/03/2018

I want to calucate the total for each month.

Country          Product                 Sales                     Qty               Sales Date       Month Sales

A	Product 1	100	10	2/01/2018
B	Product 2	200	20	2/01/2018
C	Product 3	300	30	3/01/2018
D	Product 1	400	40	5/01/2018	1000
E	Product 2	500	50	7/02/2018
A	Product 1	600	60	10/02/2018
B	Product 2	700	70	11/02/2018	1800
C	Product 2	800	80	12/03/2018
D	Product 2	850	90	14/03/2018
D	Product 3	1000	100	15/03/2018	2650

Any help would be appreciated.

[Anonymous · Accepted solution]

Right click on Calendar Date, new hierarchy, I suppose this will solve your problem.

Then you should have year, quarter, month and day available.

[Anonymous]

Hi. I have a similar situation and I want the result as the other user.  I tried the bellow options but none worked.

I want to calculate Each Employees Monthly Working Hours - therefore by employe and month-year.

I am using below formula: 

Total Hourse per month = CALCULATE(sum(working hours]);FILTER('workers table'; 'workers table'[Date].[Date] && FILTER('workers table'; 'workers table'[name and surname]))) 

getting below error 

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Can anyone help? 

Thanks a lot!!

[v-piga-msft · Accepted solution]

Hi @anandav,

As  @Anonymous mentioned, you could create a table with Date hierarchy like below then you could get Month total sales.

In addition, you could create a calculated column with the formula below.

Sales per month =
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        'Sales',
        MONTH ( 'Sales'[Sales Date] ) = MONTH ( EARLIER ( 'Sales'[Sales Date] ) )
    )
)

Then you could get your expected output.

For reference, you could have a look at this similar thread.

Best Regards,

Cherry

[Anonymous]

@v-piga-msft

Hi!

Could you just explain what the EARLIER does here? I'm a bit confused by that function TBH.

Thanks!

[anandav]

Hi @Anonymous,

Check this article. It may help you and also explain how you can use variables to avoid using EARLIER.

https://www.sqlbi.com/articles/variables-in-dax/

[Anonymous]

Do you have a Calendar Table? On the field list, do you see any "Date Hierarchy"?

[anandav]

@Anonymous,

Yes, I have a calendar table and relationsip established with Sales Date in Sales table.

No, I cannot see the date hierarchy.

[Anonymous · Accepted solution]

Right click on Calendar Date, new hierarchy, I suppose this will solve your problem.

Then you should have year, quarter, month and day available.