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.
Hi!
I have different transaction. I would like to create a calculated column which calculates the first price of the transaction. As I showed in the desired column. What DAX measure do I need to use?
Name | Date | Price | Transaction | ID | PeriodID | Desired column |
A | 1-1-2019 | 10 | Buy | 1 | 1 | 10 |
A | 2-1-2019 | 12 | 1 | 10 | ||
A | 3-1-2019 | 8 | 1 | 10 | ||
A | 4-1-2019 | 15 | Sell | 1 | 1 | 10 |
B | 1-6-2019 | 150 | Buy | 2 | 2 | 150 |
B | 2-6-2019 | 101 | 2 | 150 | ||
B | 3-6-2019 | 105 | 2 | 150 | ||
B | 4-6-2019 | 80 | 2 | 150 | ||
B | 5-6-2019 | 120 | 2 | 150 | ||
B | 6-6-2019 | 99 | Sell | 2 | 2 | 150 |
Solved! Go to Solution.
@Anonymous Here is the formula to get periodId wise min date price
Column =
VAR minDate = CALCULATE(MIN('Fact'[Date]),ALLEXCEPT('Fact','Fact'[Name],'Fact'[PeriodID]))
RETURN CALCULATE(MAX('Fact'[Price]),FILTER(ALLEXCEPT('Fact','Fact'[PeriodID]),'Fact'[Date] = minDate))
@Anonymous
Column =
VAR minDate = CALCULATE(MIN('Fact'[Date]),ALLEXCEPT('Fact','Fact'[Name]))
RETURN CALCULATE(SUM('Fact'[Price]),FILTER(ALL('Fact'),'Fact'[Date] = minDate))
@Anonymous
Hi! Thank you for response. My dummy data wasn't as clear as needed. In the name and date column, it's possible that the same value appears multiple times. Do you know how I can modify the formula, so I can get the desired column?
Name | Date | Price | Transaction | ID | PeriodID | Desired column | Column with current measure |
A | 1-1-2019 | 10 | Buy | 1 | 1 | 10 | 10 |
A | 2-1-2019 | 12 | 1 | 10 | 10 | ||
A | 3-1-2019 | 8 | 1 | 10 | 10 | ||
A | 4-1-2019 | 15 | Sell | 1 | 1 | 10 | 10 |
B | 1-6-2019 | 150 | Buy | 2 | 2 | 150 | 200 |
B | 2-6-2019 | 101 | 2 | 150 | 200 | ||
B | 3-6-2019 | 105 | 2 | 150 | 200 | ||
B | 4-6-2019 | 80 | 2 | 150 | 200 | ||
B | 5-6-2019 | 120 | 2 | 150 | 200 | ||
B | 6-6-2019 | 99 | Sell | 2 | 2 | 150 | 200 |
A | 1-6-2019 | 50 | Buy | 3 | 3 | 50 | 10 |
A | 2-6-2019 | 55 | 3 | 50 | 10 | ||
A | 3-6-2019 | 70 | 3 | 50 | 10 | ||
A | 4-6-2019 | 60 | Sell | 3 | 3 | 50 | 10 |
@Anonymous Here is the formula to get periodId wise min date price
Column =
VAR minDate = CALCULATE(MIN('Fact'[Date]),ALLEXCEPT('Fact','Fact'[Name],'Fact'[PeriodID]))
RETURN CALCULATE(MAX('Fact'[Price]),FILTER(ALLEXCEPT('Fact','Fact'[PeriodID]),'Fact'[Date] = minDate))
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |