help with looping on a table

[Anonymous]

Hello All,

I have a table that looks like the below

Part   Cost   Site      Date
A       80      EMEA   20210101
B       85      EMEA   20210101
C       75      EMEA   20210101
E       50      EMEA   20210101
A       75      EMEA   20210201
B       50      EMEA   20210201
C       80      EMEA   20210201
D       80     EMEA   20210201
E        85    EMEA   20210201

I want to have a slicer on the month.So say i select the month as Feb(20210201) I want to see the parts that were added newly in this month with price greater than 70 or if they were present in the month of Jan with price lesser than 70 but in Feb it is greater than 70.
2)want to see the part which was there in January with price greater than 70 but in Feb it has a price lesser than 70 than should be shown as deleted.so my output would be a Table like below.

Part     Action
B         Removed
D         Added
E          Added

Thanks

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

Action Measure =

SWITCH (

TRUE (),

CALCULATE ( SUM ( 'Table'[Cost] ), 'Table'[Month] = 2 ) > 70

&& CALCULATE ( SUM ( 'Table'[Cost] ), 'Table'[Month] = 1 ) < 70, "Added",

CALCULATE ( SUM ( 'Table'[Cost] ), 'Table'[Month] = 2 ) < 70

&& CALCULATE ( SUM ( 'Table'[Cost] ), 'Table'[Month] = 1 ) > 70, "Removed"

)

https://www.dropbox.com/s/q4xxntihij2ymy3/bespeck.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

@Jihwan_Kim 

Thanks.This is exactly i was looking for.I was trying to find something that went through each row but guess you had a better idea.One more thing i tried to put in a slicer for the month so that instead of giving the month number hardcoded,it would just take the month selected but somehow didnt work.Your thoughts on that would be helpful.

Thanks

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I tried to create a slicer for selecting a month.

Please check the link down below.

However, because the sample only shows two months, I am not sure whether it is properly showing the result or not.

If you have other sample pbix file, please share the link here then I can try to look into it to come up with a more accurate measure.

Thanks.

https://www.dropbox.com/s/q4xxntihij2ymy3/bespeck.pbix?dl=0 

[Anonymous]

@Jihwan_Kim 

What you have provided is great.Thanks a lot.But if there is anything will srely come back.

Thanks again