Skip to main content
cancel
Showing results for 
Search instead 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

Reply
gujralh
Helper I
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
xifeng_L
Solution Sage
Solution Sage

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

 

xifeng_L_0-1718017242772.png

 

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~

View solution in original post

7 REPLIES 7
xifeng_L
Solution Sage
Solution Sage

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

 

xifeng_L_0-1718017242772.png

 

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~

Thanks much @xifeng_L  Getting the desired output as required. 


🌟

v-yaningy-msft
Community Support
Community Support

Hi, @gujralh 

Thanks for @SaiTejaTalasila reply. You can try this.

vyaningymsft_0-1717998390430.png

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!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum


 

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! 

 

gujralh_1-1718023746689.png

 

 

gujralh
Helper I
Helper I

@SaiTejaTalasila 

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.

@xifeng_L 

could you please help me on this ?

SaiTejaTalasila
Solution Sage
Solution Sage

Hi @gujralh ,

 

Please share more details of your requirement.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.