A sum of sales that only the product sold in a different specific period

[Young_G_Han]

 Hi.

I have a problem with the dynamic two periods and the sum of sales.

My table is like...

Product     Q'ty      Period

A                1            a

B                2            a

C                1            b

A                2            b

A                1            c

For some reason, I have to get the sum of sales by two different periods.

With calculation...

Product      Sales in period a       Sales in period b

A                           1                                 2

B                           2

C                                                              1

Total                      3                                 3

I made this but I had to make another column only for the items that I sold during both a and b periods.

Product      Sales in period a       Sales in period b      %Growth

A                           1                                 2                       100%

B                           1                                                              0%

C                                                              1                           0%

Total                      3                                 3                          0%

But I want to make the table like this...

Product      Sales in period a       Sales in period b      %Growth

A                           1                                 2                       100%

Total                      1                                 2                       100%

and also need a card for the %Growth as 100%.

I can delete the items from the table with the 'filters' but I cannot use the 'filters' for a card.

[danextian]

Hi @Young_G_Han ,

You can create a calculated column to check whether a product has sales in both periods a and b

With sales in perios a and b = 
CALCULATE (
    SUM ( 'Table'[Qty] ),
    ALLEXCEPT ( 'Table', 'Table'[Product] ),
    'Table'[Period] = "a"
)
    <> BLANK ()
    && CALCULATE (
        SUM ( 'Table'[Qty] ),
        ALLEXCEPT ( 'Table', 'Table'[Product] ),
        'Table'[Period] = "b"
    )
        <> BLANK ()

.Please see sample pbix.

[Young_G_Han]

  Dear Danextian

Thank you for your help.

I created a column with the formula you informed me of.

But I have modified it below.

However, the new field has #ERROR as a value.

With sales in perios a and b =

Var PMAXDate = VALUES(PRI[Exp.Date])

Var PMINDate = VALUES(PRI[Act.Date])

Var SMAXDate = VALUES(SEC[Exp.Date])

Var SMINDate = VALUES(SEC[Act.Date])

return

CALCULATE (

    SUM (SalesDB[SYS_LINE_VALUE] ),

    ALLEXCEPT (SalesDB, SalesDB[ITEM] ),

    SalesDB[DATE] <= PMAXDate,

    SalesDB[DATE] >= PMINDate

)

    <> BLANK ()

    && CALCULATE (

        SUM(SalesDB[SYS_LINE_VALUE] ),

        ALLEXCEPT (SalesDB, SalesDB[ITEM] ),

        SalesDB[DATE] <= SMAXDate,

        SalesDB[DATE] >= SMINDate

    )

        <> BLANK ()

The Error message is...

A table of multiple values was supplied where a single value was expected.

I think this is caused by the period range. But I need multiple periods.

Can you advise?

Thank you in advance.

[danextian]

hi @Young_G_Han ,

The solution initially proposed was based on the sample data. Can you please post a sample data that actually represents your data?

[Young_G_Han]

  Dear Danextian

The following is the sample data.

And the period must be changed based on users' selection.

Period A: 2022-10-04 to 2023-02-07

Period B: 2020-01-02 to 2021-02-14

ITEM ID	CUSTOMER	CUST_LINE_VALUE	Cust. Country	ORD_OPEN_DATE
3601064	A	571.48	USA	2017-02-23 0:00
3601119	A	715.22	USA	2017-05-05 0:00
3601119	A	719.22	USA	2017-06-29 0:00
3601119	A	721.36	USA	2017-08-04 0:00
3601119	A	750.7	USA	2019-03-29 0:00
3601119	A	771.62	USA	2020-03-04 0:00
3601119	A	411.73	USA	2021-02-25 0:00
3601119	A	443.57	USA	2022-07-22 0:00
3601119	A	439.66	USA	2022-07-26 0:00
3601120	A	394.64	USA	2020-06-09 0:00
3601120	A	403.11	USA	2021-04-01 0:00
3601120	A	473.24	USA	2022-12-21 0:00
3601127	A	715.22	USA	2017-05-05 0:00
3601127	A	719.22	USA	2017-06-29 0:00
3601223	A	456.67	USA	2016-02-03 0:00
3601228	A	408.06	USA	2015-03-11 0:00
3601228	A	409.42	USA	2015-03-26 0:00
3601228	A	883.3	USA	2018-04-04 0:00
3601228	A	987.68	USA	2021-04-01 0:00
3601431	A	818.84	USA	2015-03-26 0:00
3601613	A	373.46	USA	2019-10-22 0:00
3601618	A	403.11	USA	2021-04-01 0:00
3601622	A	966.7	USA	2022-10-25 0:00
3602968	A	493.84	USA	2021-04-01 0:00
3604859	A	686.46	USA	2016-03-24 0:00
3604859	A	1378.32	USA	2018-01-30 0:00
3604859	A	723.09	USA	2019-06-19 0:00
3604859	A	1601.2	USA	2021-08-11 0:00
6181902	A	633.28	USA	2020-03-06 0:00
6181902	A	980.08	USA	2020-03-13 0:00
6341178	A	1110.38	USA	2020-12-22 0:00

[v-yueyunzh-msft · Accepted solution]

Hi , @Young_G_Han 

Here are the steps you can refer to :
(1)This is my test data:

(2)I create two date table as two slicers , like this:

(3)Then we need to create three measures to put on the visual:

Sales in period a = var _min_date = MIN('A SLicer'[Period A])
 var _max_date = MAX('A SLicer'[Period A])
 var _t = FILTER('Table' ,'Table'[ORD_OPEN_DATE]>= _min_date && 'Table'[ORD_OPEN_DATE]<= _max_date)
 return
 SUMX(_t ,[CUST_LINE_VALUE])

Sales in period b = var _min_date =MIN('B SLicer'[Period B])
 var _max_date = MAX('B SLicer'[Period B])
 var _t = FILTER('Table' ,'Table'[ORD_OPEN_DATE]>= _min_date && 'Table'[ORD_OPEN_DATE]<= _max_date)
 return
 SUMX(_t ,[CUST_LINE_VALUE])

%Growth = IF([Sales in period a] && [Sales in period b] , DIVIDE( [Sales in period b]-[Sales in period a] , [Sales in period a]) )

Then we can get first need:

(4)We can create another measure to filter the blank in this visual:

Flag = IF([Sales in period a]<> BLANK() && [Sales in period b] <> BLANK() ,1,0)

Then we can configure the measure on the "Filter on this visual" ,then  the second need we can get:

(5)In the end , we can create a measure in the card visual:

Measure in a Card = var _t= SUMMARIZE('Table','Table'[ITEM] , "Sales in a",[Sales in period a],"Sales in b",[Sales in period b])
var _t2= FILTER(_t,[Sales in period a]<> BLANK() && [Sales in period b]<> BLANK())
return
DIVIDE( SUMX(_t2,[Sales in period b])-SUMX(_t2,[Sales in period a]) , SUMX(_t2 ,[Sales in period a]))

Then we can meet your last need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Young_G_Han]

I referred to your pbix file and it is perfectly working.

Thank you so much!!!