Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

help with looping on a table

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

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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"
    )
     
     

    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

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    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"
    )
     
     

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        5 years ago

        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