cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Mark YES for all item if condition met for last date

How to mark yes for item of all dates if last date condition is met in power bi ?

Item   Date       Value  Results (>10 lastdate)

A.     June 6th      30       Yes

A.     June 5th.      5        Yes

A.     June 4th.     14        Yes

A.     June 3rd.     4          Yes

B.     June 6th      5           No

B.     June 5th.      20        No

B.     June 4th.     30         No

B.     June 3rd.     4           No

1 ACCEPTED SOLUTION
Solution Sage

Share another method for it, can refer to below calculated column.

``````Result =
IF(
CALCULATE(
CALCULATE(
SUM('Table'[Value]),
LASTDATE('Table'[Date])
),
ALLEXCEPT('Table','Table'[Item])
)>10,
"Yes",
"No"
)``````

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

7 REPLIES 7
Solution Sage

Share another method for it, can refer to below calculated column.

``````Result =
IF(
CALCULATE(
CALCULATE(
SUM('Table'[Value]),
LASTDATE('Table'[Date])
),
ALLEXCEPT('Table','Table'[Item])
)>10,
"Yes",
"No"
)``````

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Helper I

Thanks much @xifeng_L  Getting the desired output as required.

🌟

Community Support

Hi, @gujralh

Thanks for @SaiTejaTalasila reply. You can try this.

Measure:

``````If > 10 =
VAR _getDate =
SELECTEDVALUE ( 'Table'[Date] )
VAR _getItem =
SELECTEDVALUE ( 'Table'[Item] )
VAR _getValue =
SELECTEDVALUE ( 'Table'[Value] )
VAR _maxDate =
CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Item] = _getItem, ALL () )
VAR _flagValue = 10
VAR _value =
CALCULATE ( SUM ( 'Table'[Value] ), _maxDate = 'Table'[Date] )
VAR _result =
IF ( _value > _flagValue, "yes", "no" )
RETURN
_result
``````

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Helper I

Thanks @v-yaningy-msft , I tried this way but it is giving "NO" for all condition. When am doing this with measure, it is showing long list of dates which was even not present in my database.

Output with calculated column as below, which is also not getting satisfied with condition. Could you please let me know where I'm going wrong. Thanks!

Helper I

I have tabular data as Item, Date and Value. And I want to get the output as results column! i.e mark other dates of same items also 'yes' if condition satisfied based on last date with value say '>10' else 'No' for all dates of that item.

Helper I

Solution Sage

Hi @gujralh ,