The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
goal: find the newst unit price for a customer.
I have a sales unit price tabel that is conected to a customer tabel. it is currently not connected to the date table.
the sales unit price have a customer number, startdate, enddate and unitprice.
customer number | startdate | end date | unitprice |
1 | 1000 | ||
2 | 31/12/2023 | 1700 | |
2 | 1/1/2024 | 1600 | |
3 | 1/1/2024 | 1200 | |
4 | 1/1/2024 | 1/1/2025 | 2000 |
as u can see 1) the start date and end date can both be empty. 2) start date and end date can be in 2 seperate rows. 3) start date can be filled but there is no end date and 4) both start date and end date can be filled on the same row.
I made this code but it only works when start date and end date is in two rows
Measure =
CALCULATE (
SUMX (
VALUES ( sales unit price[customer] ),
SUM ( sales unit price[Unit_Price] )
),
FILTER (
sales unit price,
MAX ( sales unit price[Starting_Date] )
>= MAX ( sales unit price[End_Date] )
)
)
the end reuslt should be to return a measure the newest unitprice
customer | measure to calculate unitprice |
1 | 1000 |
2 | 1600 |
3 | 1200 |
4 | 2000 |
Solved! Go to Solution.
@MathiesJeppesen Lookup Min/Max - Microsoft Fabric Community
Hi @MathiesJeppesen ,
Thank you for @Greg_Deckler answer , and I have other suggestions for you:
Below is my table:
The following DAX might work for you:
Measure =
VAR _max_start = CALCULATE(MAX('Table'[startdate]),ALLEXCEPT('Table','Table'[customer number]))
VAR _max_end = CALCULATE(MAX('Table'[end date]),ALLEXCEPT('Table','Table'[customer number]))
VAR result = IF(_max_start >= _max_end , CALCULATE(MAX('Table'[unitprice]),FILTER('Table','Table'[startdate] = _max_start),ALLEXCEPT('Table','Table'[customer number])), CALCULATE(MAX('Table'[unitprice]),FILTER('Table','Table'[end date] = _max_end),ALLEXCEPT('Table','Table'[customer number])))
RETURN
result
The final output is shown in the following figure:
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MathiesJeppesen ,
Thank you for @Greg_Deckler answer , and I have other suggestions for you:
Below is my table:
The following DAX might work for you:
Measure =
VAR _max_start = CALCULATE(MAX('Table'[startdate]),ALLEXCEPT('Table','Table'[customer number]))
VAR _max_end = CALCULATE(MAX('Table'[end date]),ALLEXCEPT('Table','Table'[customer number]))
VAR result = IF(_max_start >= _max_end , CALCULATE(MAX('Table'[unitprice]),FILTER('Table','Table'[startdate] = _max_start),ALLEXCEPT('Table','Table'[customer number])), CALCULATE(MAX('Table'[unitprice]),FILTER('Table','Table'[end date] = _max_end),ALLEXCEPT('Table','Table'[customer number])))
RETURN
result
The final output is shown in the following figure:
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HI
It looks to be working and i will dobbel check it tomorrow! thx a lot
@MathiesJeppesen Lookup Min/Max - Microsoft Fabric Community
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
264 | |
120 | |
115 | |
83 | |
70 |