Yearly sum takes values of other years

[Anonymous]

Hello,

I need help to build a measure used in a difference from a data set. I have the following data:

OWNER	MONTH	YEAR	PRODUCT	NUMBER
PETER	Oct	2021	FRUIT	5
PETER	Oct	2021	HERBS	6
PETER	Oct	2021	CARROT	3
PETER	Oct	2021	TOMATO	4
PETER	Oct	2021	PEACH	8
PETER	Oct	2021	APPLE	9

OWNER	MONTH	YEAR	PRODUCT	NUMBER	SUM 2019
PETER	Oct	2019	BERRY	10	110
PETER	Oct	2019	PINEAPPLE	6	110
PETER	Oct	2019	WATERMELON	2	110
PETER	Oct	2019	CORN	3	110
PETER	Oct	2019	LETTUCE	9	110
PETER	Oct	2019	PEAS	80	110

The problem I'm having is that I need to build a measure which only calculates the difference between any 2021 value (or any year fot hat matter), vs 2019 total sum ONLY.

When I build the metric I obtain the following:

OWNER	MONTH	YEAR	PRODUCT	NUMBER	SUM 2019	DIFFERENCE
PETER	Oct	2021	BERRY		110	-110
PETER	Oct	2021	PINEAPPLE		110	-110
PETER	Oct	2021	WATERMELON		110	-110
PETER	Oct	2021	CORN		110	-110
PETER	Oct	2021	LETTUCE		110	-110
PETER	Oct	2021	PEAS		110	-110
PETER	Oct	2021	FRUIT	5	110	-105
PETER	Oct	2021	HERBS	6	110	-104
PETER	Oct	2021	CARROT	3	110	-107
PETER	Oct	2021	TOMATO	4	110	-106
PETER	Oct	2021	PEACH	8	110	-102
PETER	Oct	2021	APPLE	9	110	-101

As you can see, the new table is considering all rows products from 2019 when I only need 2021 (or any other year that I choose) exclusively since I will chart the difference between fixed 2019 sum and the number from 2021.

Couple of things to consider:

- Data is under Live Connection so I can't modify or add any data/tables/etc.

- All the metrics (OWNER, PRODUCT, YEAR, MONTH, ETC) come from different tables in the original data set (except YEAR and MONTH that come from "Calendar" table)

- I can't just "Filter" the table to "not show blank values" under "NUMBER" column.

Please let me know any options I could use to calculate the difference and just report it for 2021 (or any other year), the idea is that SUM 2019 is a fixed value (constant) not affected by any slicers or filter, while the other data is, and can be substracted to any metric I choose (NUMBER in this case).

Thank you for your support.

[Ashish_Mathur]

Hi,

Try this approach.  Remove the last column from the second table.  Using the Query Editor, append both tables.  In the appended table, create another column of proper date entries (from the Year and Month column - assume the date to be 1).  Create a Calendar Table and build a relationship from the Date column of the appended table to the Date column of the Calendar Table.  In the Calendar Table, write calculated column formulas to build a Year column.  To your visual, drag Year and Month from the Calendar Table & drag Product and Owner from the appended dataset.  Write these measures

Total = sum(Data[Number])

Total in 2019 = calculate([Total],calendar[year]=2019)

DIfference = [total]-[Total in 2019]

Hope this helps.

[Anonymous]

Hello, 

I am not able to manipulate the tables or even the Query Editor on Power BI since they are all grayed out and its a live connection file, so I can't do the first steps of what you mentioned.

The tables I made to ask the questions are results in a visual I created in the worksheet dragging the different metrics to it (from different tables).

Any sugestions? Thank you.

[v-rzhou-msft]

Hi @Anonymous 

Due to you use live connection mode, you may create measures to achieve your goal.

Could you show me more details about your tables? A screenshot or sample data may be better.

You can provide me with a sample data by your onedrive for business.

This may make it easier for me to understand your data model and your requirement.

Best Regards,

Rico Zhou

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

[amitchandak]

@Anonymous , In that case choose 2021 in year slicer. Better to year in a separate Table ot date table

Try a meausre like

measure =
var _year = 2019
return
sum(Table[NUMBER]) - calculate(sum(Table[NUMBER]), filter(all(Table[Year]), Table[Year] =2019))

or

measure =
var _year = 2019
return
sum(Table[NUMBER]) - calculate(sum(Table[NUMBER]), filter(all(Table), Table[Year] =2019))

[Anonymous]

Hello,

The metrics shown in the example are from different tables, not from the same, "Table", also, this formula is only showing the values for 2020, not the actual difference, is it possible that there could be a mistake?