Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
I would like to return "Value2" by max date and by Category "Name"
and then return the respective value from column "Value1"
I also want to compare the Value2 column max date values and return
the non-similar values by the max date concept by category.
and then return the respective value from column "Value1"
The output should be like this
Name | DDMMYY | Value1 | Vaue2 |
A | 1/1/2023 | 5 | 2 |
A | 1/2/2023 | 5 | 2 |
A | 1/3/2023 | 5 | 2 |
A | 1/4/2023 | 6 | 3 |
A | 1/5/2023 | 8 | 5 |
A | 1/6/2023 | 8 | 6 |
A | 1/7/2023 | 8 | 6 |
B | 1/1/2023 | 7 | 3 |
B | 1/2/2023 | 7 | 3 |
B | 1/3/2023 | 7 | 3 |
B | 1/4/2023 | 8 | 4 |
B | 1/5/2023 | 10 | 6 |
B | 1/6/2023 | 10 | 7 |
B | 1/7/2023 | 10 | 7 |
Solved! Go to Solution.
Hi @ArpitVijay
It is nor very clear and the sample data might be a little misleading. Howeve, please refer to attached sample file with the proposed solution
Value X =
SUMX (
VALUES ( 'Table'[Name] ),
MAXX (
TOPN (
1,
CALCULATETABLE ( 'Table' ),
'Table'[DDMMYY]
),
'Table'[Value2]
)
)
Value Y =
SUMX (
VALUES ( 'Table'[Name] ),
MAXX (
TOPN (
1,
TOPN (
1,
CALCULATETABLE ( 'Table' ),
'Table'[Value1]
),
'Table'[DDMMYY],
ASC
),
'Table'[Value2]
)
)
Value1 is right however Value2 can't be calculated by top n I think.
Max date by category is 1.6.2023
and the "Value X" for Category a is 6 and category b is 4 colored in orange
now I would like to find the Value Y based on Max date by category and
the value should not equal to Previous Value "Value X"
Value1 is right however Value2 can't be calculated by top n I think.
Max date by category is 1.6.2023
and the "Value X" for Category a is 6 and category b is 4 colored in orange
now I would like to find the Value Y based on Max date by category and
the value should not equal to Previous Value "Value X"
May you please check I found your solution to be useful here.
@ArpitVijay- Please take a look at this DAX measure and let me know if it works for you. I'm uncertain if the results displayed by you are accurately calculated. Do let me know if there's something I might have overlooked.
VALUE X =
VAR MaxDate = CALCULATE(MAX('Table'[DDMMYY]), ALL('Table'))
VAR MaxValue2BeforeLastDate = CALCULATE(MAX('Table'[Vaue2]), 'Table'[DDMMYY] <> MaxDate)
RETURN MaxValue2BeforeLastDate
VALUE Y =
VAR MaxDate = CALCULATE(MAX('Table'[DDMMYY]), ALL('Table'))
VAR MaxValue1BeforeLastDate = CALCULATE(MAX('Table'[Value1]), 'Table'[DDMMYY] <> MaxDate)
RETURN MaxValue1BeforeLastDate
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
22 | |
20 | |
20 | |
13 | |
13 |
User | Count |
---|---|
41 | |
28 | |
25 | |
23 | |
21 |