How to calculate measure for stock value for items that are only created in current year

[Niels_T]

Hello,

I would like to calculate 2 measures.

1. A measure that makes a SUM of all stock values of only the items that are created this year
2. A measure that makes a SUM of all stock values of all the items except the items that are created this year.

I wanted to make these two measures to see what the current value of the stock is of the new items compared to the old items.

With these two measures I'll be able to put those two in a pie chart and I can compare them.

I have the following tables:

Item table:

- Description

- Release date

- Item No.

Stock value:

- Cost amount

- Release date

- Item No.

Best regards,

Niels

[v-angzheng-msft]

Hi, @Niels_T 

May I ask if your problem has been solved? Is the above answer helpful to you?
If it helps, could you please mark the response which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

[SanketBhagwat]

Hi @Niels_T .

1)To calculate Sum of items for the current year, you can make use of the below DAX;

SumCurrentYear=Calculate(SUM(table[Stocks]),'DateTable'[Year]=MAX('DateTable'[Year])

2)To calculate SUM of stocks except for current year, you can use;

SumYear=Calculate(SUM(table[stocks]),'DateTable'[Year]<MAX('DateTable'[Year])

If this post helps, then please mark it as 'Accept as Solution' and give it a big thumbs up.

Regards,

Sanket Bhagwat.

[Niels_T]

I get the following error:

[SanketBhagwat]

ay.

In that case you can add  a FILTER function in there;

SumCurrentYear=Calculate(SUM(table[Stocks]),Filter(DateTable','DateTable'[Year]=MAX('DateTable'[Year])

If that too is not helping, then I would want you to visit https://community.powerbi.com/t5/Desktop/Error-Message-in-Calculation-A-function-MAX-has-been-used-i... in order to solve that error.

Regards,

Sanket Bhagwat

[Niels_T]

Hello Sanket,

The measure now works with the filter function.

However, this one 

Also "works" but doesn't show me any results.

[SanketBhagwat · Accepted solution]

Okay.

I have another workaround.

1)Create a measure for current year as CurrentYear=YEAR(MAX(DateTable[Date])).

This will give you the current year.

2) Now use the same measure with  a bit of modification;

CurrentYearSales=CALCULATE(SUM(Table[Sales]),YEAR(DateTable[Date])=2015)

3) SalesLastYear= CALCULATE(SUM(Table[Sales]),YEAR(DateTable[Date])<2015).

I know this will defintely work.

If this post helps you, then please mark it as 'Accept as Solution' so that other users could find it easily and give it a big thumbs up.

Regards,

Sanket Bhagwat