Forum Discussion
Remove rows based on condition
Hi PBI_newuser ,
Please modify this formula ：Current Year.
Current year = VAR result1_ = SUMX ( FILTER ( ALLSELECTED ( 'Append Table' ), 'Append Table'[next_count] = BLANK () && 'Append Table'[last_count] = BLANK () && [min_] = 3 && 'Append Table'[Fis] = SELECTEDVALUE ( 'Calendar'[Fiscal Year] ) ), [Revenue Current Year] ) RETURN result1_Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi PBI_newuser ,
Please try to see if it helps you.
Create measures.
Measure 3=
SUMX (
FILTER (
ALLSELECTED ( 'Append Table' ),
'Append Table'[Fis] = SELECTEDVALUE ( 'Calendar'[Fiscal Year] )
&& 'Append Table'[Contract Number]
= SELECTEDVALUE ( 'Append Table'[Contract Number] )
),
[Revenue Current Year]
)
Measure 2 = IF(ISBLANK([Measure 3]),1,BLANK())Then filter the data.
Then Create measures.
min_ =
CALCULATE (
DISTINCTCOUNT ( 'Append Table'[YearMonth] ),
FILTER (
ALLSELECTED ( 'Append Table' ),
'Append Table'[Contract Number]
= SELECTEDVALUE ( 'Append Table'[Contract Number] )
&& 'Append Table'[Fis] = SELECTEDVALUE ( 'Calendar'[Fiscal Year] )
)
)
next_count =
CALCULATE (
DISTINCTCOUNT ( 'Append Table'[YearMonth] ),
FILTER (
ALLSELECTED ( 'Append Table' ),
'Append Table'[Contract Number]
= SELECTEDVALUE ( 'Append Table'[Contract Number] )
&& RIGHT ( 'Append Table'[Fis], 4 ) + 0
= RIGHT ( SELECTEDVALUE ( 'Calendar'[Fiscal Year] ), 4 ) + 1
)
)
last_count =
CALCULATE (
DISTINCTCOUNT ( 'Append Table'[YearMonth] ),
FILTER (
ALLSELECTED ( 'Append Table' ),
'Append Table'[Contract Number]
= SELECTEDVALUE ( 'Append Table'[Contract Number] )
&& RIGHT ( 'Append Table'[Fis], 4 ) - 0
= RIGHT ( SELECTEDVALUE ( 'Calendar'[Fiscal Year] ), 4 ) - 1
)
)
Current year =
VAR result1_ =
SUMX (
FILTER (
ALLSELECTED ( 'Append Table' ),
[min_] = 3
&& 'Append Table'[next_count] = BLANK ()
&& 'Append Table'[Fis] = SELECTEDVALUE ( 'Calendar'[Fiscal Year] )
),
[Revenue Current Year]
)
VAR result2_ =
SUMX (
FILTER (
ALLSELECTED ( 'Append Table' ),
'Append Table'[next_count] = BLANK ()
&& 'Append Table'[last_count] = 3
&& 'Append Table'[Fis] = SELECTEDVALUE ( 'Calendar'[Fiscal Year] )
),
[Revenue Current Year]
)
RETURN
IF ( ISBLANK ( result2_ ), result1_, result2_ )
If I have misunderstood your meaning, please provide contact me with your desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous , Thank you so much for the solution. The table below seems correct but the "Current year" measure for FY2022 is incorrect. After removing the unecessary rows, the revenue for FY2021 = 12097 is correct but FY2022 "Current year" figure should show up as 12315.
Hi PBI_newuser ,
If you have solve your problem, please share your way and mark your answer as the answer so it will be easier to search and be able to help more people.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous , I haven't solve it. I want to sum the revenue after removing those rows and show it in the top table. How to get it?