Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I would like to calculate 2 measures.
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
Solved! Go to 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
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.
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.
I get the following error:
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
Hello Sanket,
The measure now works with the filter function.
However, this one
Also "works" but doesn't show me any results.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |