Forum Discussion

Aknas's avatar
Aknas
Frequent Visitor
3 years ago
Solved

Newbies question - Multiple buckets

Hi all,

I'm trying to create buckets - but found difficult how to only get the last 3- month by ID. Basically wil only select the last 3- months to creat the buckets.

 

Here is the sample Data link - 

 

https://1drv.ms/x/s!Arm2JbpOh1VdgSU-gkfBnsXNKehF?e=tqzfSH 

 

I was able to create one by one buckets but thats not going to help what I'm trying to achieve.

 

Please  see if you can be help on this.

 

Dii

 

 

Need Help
  • AbbasG's avatar
    AbbasG
    3 years ago

    Aknas

    Here is the URL to my solution - Aknas multiple buckets solutions.pbix

    I hope it helps. Please accept it as a solution if it helps you achieve your desired output.

     

    For creating buckets I have added a conditional column, you can find it in the power query applied steps.

     

    For filtering the dates, I have used TOP N on each of the corresponding IDs.

     

     

5 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    3 years ago

    Hey Aknas ,

     

    provide sample data that can easily be copied to pbix file, describe the expected result based on the sample data you provide.

     

    Regards,

    Tom

  • AbbasG's avatar
    AbbasG
    Memorable Member
    3 years ago

    Aknas

    Here is the URL to my solution - Aknas multiple buckets solutions.pbix

    I hope it helps. Please accept it as a solution if it helps you achieve your desired output.

     

    For creating buckets I have added a conditional column, you can find it in the power query applied steps.

     

    For filtering the dates, I have used TOP N on each of the corresponding IDs.

     

     

    • Aknas's avatar
      Aknas
      Frequent Visitor
      3 years ago

      Thanks Abbas.

      Is it possible to capture the last three months for each ID ( The month list can be dynamic) : 

      Right now I have a static conditional column [ Month Key]  but that's not true always.

       

      https://1drv.ms/u/s!Arm2JbpOh1VdgScd8jJYOhMl1j8V?e=SBMRpi

        

         

      then want to change the below DAX formula  to capture the last 3-month only

      Benefit Bucket =
      SWITCH (
          TRUE (),
         [Month Key]=1
              && [Benfit]>=0.80, "top",
            [Month Key]=1
              && [Benfit]>0.7 && [Benfit]<0.80, "average",
        [Month Key]=1
              && [Benfit]>0.6 && [Benfit]<0.70, "poor","outlier"
      )
       
      thanks again