Difference current month / month a year back on customer level in Power BI

user_11340304 0
Viewed 71

I am trying to calculate the sales volume difference between a given month and the same month one year back (e.g Jan 2021 - Jan 2020). The tricky part is that it should be done on customer level. My table now looks like this:

Date Customer_number Sales_Volume
14.01.2020 1111 1000
13.01.2020 2222 2000
21.02.2020 1111 4000
17.02.2020 2222 5000
22.01.2021 1111 1500
24.01.2021 2222 2600
19.02.2021 1111 4700
18.02.2021 2222 5800

And my output should look like this:

Date Customer_number Sales_Volume Volume_Difference
14.01.2020 1111 1000 0
13.01.2020 2222 2000 0
21.02.2020 1111 4000 0
17.02.2020 2222 5000 0
22.01.2021 1111 1500 500
24.01.2021 2222 2600 600
19.02.2021 1111 4700 700
18.02.2021 2222 5800 800
date-difference dax powerbi-desktop powerbi date
2 Answers

Try this below Measure Code-

your_value = 

var current_row_year = YEAR(MIN(your_table_name[Date]))
var current_row_month = MONTH(MIN(your_table_name[Date]))
var current_row_customer_number = MIN(your_table_name[Customer_number])
var current_row_sales_value = MIN(your_table_name[Sales_Volume])

var last_year_sales_volumne = 
CALCULATE(
    SUM(your_table_name[Sales_Volume]),
    FILTER(
        ALL(your_table_name),
        YEAR(your_table_name[Date]) = current_row_year - 1
        && MONTH(your_table_name[Date]) = current_row_month
        && your_table_name[Customer_number] = current_row_customer_number
    )
) + 0

RETURN IF(
    last_year_sales_volumne = 0,
    0,
    current_row_sales_value - last_year_sales_volumne
)

Here is the output-

enter image description here

user_36523450

This is for a calculated column:

Column = 
VAR _y = YEAR([Date])
VAR _m = MONTH([Date])
VAR _c = [Customer_number]
VAR _tbl_prev = FILTER(ALL('Table'), [Customer_number] = _c && MONTH([Date]) = _m && YEAR([Date]) = _y - 1)
VAR _tbl_curr = FILTER(ALL('Table'), [Customer_number] = _c && MONTH([Date]) = _m && YEAR([Date]) = _y)
VAR _result = SUMX(_tbl_curr, [Sales_Volume]) - SUMX(_tbl_prev, [Sales_Volume])
RETURN IF(COUNTROWS(_tbl_prev), _result, 0)

enter image description here

user_27535010
Related