Sum history sales based on current filter context

[VuongLM93]

Dear All

I have a sales table monthly volume with 2 salesperson : A, B

I have 2 customer 1, customer 2 . After 2019 to 2020 , the customers are allocated from A to B .

Now in a matrix visual -> I want to see the sales of customer of "B" in current month (for example 7 .2020 ) , but also sum their total volume in 2019 (which was allocated by A thus cannot show for B ).

	SalesPerson	2020	2019
	Month	7	7
Current on my report (wrong outcome)	A	0	30
	B	70	0  ***
My desired outcome	A	0	30
	B	70	30 ***

Customer Code	SalesPerson	Month	Year	Sales
Customer 1	A	7	2019	10
Customer 2	A	7	2019	20
Customer 1	B	7	2020	30
Customer 2	B	7	2020	40

[VuongLM93]

@v-yingjl  Unfortunately - I cannot create a table ( because the dataset is not mine. I am connecting to live dataset

Therefore I can only create measures,

[v-yingjl · Accepted solution]

Hi @VuongLM93 ,

You can create a calculated table first:

Test =
SUMMARIZE ( 'Table', 'Table'[Year], 'Table'[Month] )

Create this measure:

Measure =
SWITCH (
    SELECTEDVALUE ( 'Table'[Sales Person] ),
    "A",
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Month] = SELECTEDVALUE ( 'Test'[Month] )
                    && 'Table'[Year] = SELECTEDVALUE ( 'Test'[Year] )
                    && 'Table'[Sales Person] = "A"
            )
        ),
    "B",
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Month] = SELECTEDVALUE ( 'Test'[Month] )
                    && 'Table'[Year] = SELECTEDVALUE ( 'Test'[Year] )
            )
        )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

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

[amitchandak]

@VuongLM93 , better create a separate year table and join it with the year of your table

and try a measure like

Cumm Sales = CALCULATE(SUM(Sales[Sales]),filter(all(Year[Year]),Year[Year] <=max(Year[Year])))

Cumm Sales = CALCULATE(SUM(Sales[Sales]),filter(allselected(Year[Year]),Year[Year] <=max(Year[Year])))

or with your current table

Cumm Sales = CALCULATE(SUM(Sales[Sales ]),filter(all(table[Year]),table[Year] <=max(table[Year])))

[VuongLM93]

Why in your formula use filter by year

Does it work if i need to show different month?