Forum Discussion
Create flag based on any value from first column and filter on date column
Hello all,
I'm not sure to know how to describe properly my need but I'll try to be clear with a specific example:
| part | valid from | flag |
| A | 24/02/2024 | N |
| A | 25/12/2023 | Y |
| A | 26/11/2023 | N |
| B | 24/02/2024 | N |
| B | 25/12/2023 | Y |
| B | 26/11/2023 | N |
| C | 24/02/2024 | N |
| C | 25/12/2023 | Y |
| C | 26/11/2023 | N |
Here is the final result. I want to create the flag column based on part and valid from column.
I want to flag to Y this new column the max(valid from) before 01/01/2024 for each part.
Do you guys know how to achieve a such dax formula ?
Anonymous Review the following screenshot and Column and Measure.
Flag Measure =
VAR MaxValidFromBefore2024 =
CALCULATE(
MAX('Table'[valid from]),
FILTER(
ALL('Table'),
'Table'[part] = MAX('Table'[part]) &&
'Table'[valid from] < DATE(2024, 1, 1)
)
)
RETURN
IF(
MAX('Table'[valid from]) = MaxValidFromBefore2024,
"Y",
"N"
)Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
3 Replies
- fahadqadir3
Solution Supplier
Anonymous Review the following screenshot and Column and Measure.
Flag Measure =
VAR MaxValidFromBefore2024 =
CALCULATE(
MAX('Table'[valid from]),
FILTER(
ALL('Table'),
'Table'[part] = MAX('Table'[part]) &&
'Table'[valid from] < DATE(2024, 1, 1)
)
)
RETURN
IF(
MAX('Table'[valid from]) = MaxValidFromBefore2024,
"Y",
"N"
)Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!! Hi Anonymous
Thanks for the reply from fahadqadir3 .
You can also try the following:
Measure:
FlagM = VAR _date = DATE(2024, 1, 1) VAR _maxDate = CALCULATE(MAX([valid from]), FILTER(ALLEXCEPT('Table', 'Table'[part]), [valid from] < _date)) RETURN IF(MAX('Table'[valid from]) = _maxDate, "Y", "N")Column:
FlagC = VAR _date = DATE(2024, 1, 1) VAR _maxDate = CALCULATE(MAX([valid from]), FILTER(ALLEXCEPT('Table', 'Table'[part]), [valid from] < _date)) RETURN IF('Table'[valid from] = _maxDate, "Y", "N")Output:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ll, thanks for your help. The solutions looks nice but seems to not work in my case. I'm trying to do it on a semantic model connected in direct query. I have a cirular depandancy error when I'm trying the column, and for the measure it seems to duplicate my values with date from other part. The data are not ordered as in the example also.
Any other suggestions ?