Calculating an agregated sum and adding a value to the sum

[Anonymous]

Hi, I have the following problem.

I have a list of sales in current month for different products (let's say A, B, C):

Table Name: Sales_current_mnth

Product name	QTY	Value
A	5	10
B	10	10
A	1	2
C	5	15
A	1	2
C	10	30
A	25	50
B	10	10

Additionally, I have an aggregated list of sales from previous months:

Table Name: Sales_before_mnth

Product name	QTY	Value
A	100	200
B	50	50
C	30	90

I need to calculate a measure:

1) calculeate the sales numbers from the current month 

2) sum the calculated values and the aggrgated from the prevous months

3) return a table with QTYs and values by product name. 

Is it possible to calculate in DAX?

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please refer to attached sample file with the solution

Total QTY = SUM ( Sales_before_mnth[QTY] ) + SUM ( Sales_current_mnth[QTY] )

Total Sales = SUM ( Sales_before_mnth[Value] ) + SUM ( Sales_current_mnth[Value] )

[tamerj1 · Accepted solution]

@Anonymous 

Total Sales Conditional = 
IF ( 
    SELECTEDVALUE ( Sales_before_mnth[Product Name] ) = "A", 
    SUM ( Sales_before_mnth[Value] ) + SUM ( Sales_current_mnth[Value] ), 
    0
)

[Anonymous]

Additionally, I have added your formula to my work data (which is much larger, then test data) and there this measure does not calculate sums over product name. The result looks like this (for example for product A):

Total Value (A) = Value current month(A) + Sum(Values before month)

It's obvious, I have a problem in data releationship, but I can't really track it.   

[tamerj1 · Accepted solution]

@Anonymous 
Make sure the product names in both tables have exactly the same splilling. Adiitionally in order to have correct totals please try

Total Sales Conditional =
SUMX (
    VALUES ( Sales_before_mnth[Product Name] ),
    IF (
        Sales_before_mnth[Product Name] = "A",
        CALCULATE (
            SUM ( Sales_before_mnth[Value] ) + SUM ( Sales_current_mnth[Value] )
        ),
        0
    )
)

[Anonymous]

Hi, it looks that if there is a new product name is added to the current mnth table then the formula calculeats total of the sales_before_month, e.g. if there is a new product D added:

Product name	QTY	Value
A	5	10
B	10	10
A	1	2
C	5	15
A	1	2
C	10	30
A	25	50
B	10	10
D	5	100

then I get the total (formula does not find D in the  Sales_before_mnth). So how can I handle the exception in this case?

[tamerj1]

Hi @Anonymous 

please try by activating bi-directional relationship and slice by Sales_current_mnth[Product Name] the formula would be

Total Sales Conditional =
SUMX (
VALUES ( Sales_current_mnth[Product Name] ),
IF (
Sales_current_mnth[Product Name] = "A",
CALCULATE (
SUM ( Sales_before_mnth[Value] ) + SUM ( Sales_current_mnth[Value] )
),
0
)
)

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please refer to attached sample file with the solution

Total QTY = SUM ( Sales_before_mnth[QTY] ) + SUM ( Sales_current_mnth[QTY] )

Total Sales = SUM ( Sales_before_mnth[Value] ) + SUM ( Sales_current_mnth[Value] )

[Anonymous]

Thank you very much! Can you also advise, how I can create conditional measure? Let's say if Product Name = "A", then do calculation, else return 0. My 1st try was not successful:

Total Sales Conditional = IF(Sales_before_mnth[Product Name]="A", SUM ( Sales_before_mnth[Value] ) + SUM ( Sales_current_mnth[Value] ), 0)

[tamerj1 · Accepted solution]

@Anonymous 

Total Sales Conditional = 
IF ( 
    SELECTEDVALUE ( Sales_before_mnth[Product Name] ) = "A", 
    SUM ( Sales_before_mnth[Value] ) + SUM ( Sales_current_mnth[Value] ), 
    0
)