Forum Discussion

kdaya's avatar
kdaya
Icon for Helper I rankHelper I
8 years ago

How to view first 3 months

Hi Power BI Community,

 

Please can someone help me on how i can view the first 3 months of data:

 

basically each customer has a different starting date so i cannot use the slicer as this uses the entire date range from the first customer and the relative date filter only allows last not first.

 

Please can someone help

 

Thank You

Kunal

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

13 Replies

  • pxg08680's avatar
    pxg08680
    Icon for Resolver III rankResolver III
    8 years ago

    kdaya

    Can you share a screenshot of your data(sample data)..? It would be easy that way.

    • kdaya's avatar
      kdaya
      Icon for Helper I rankHelper I
      8 years ago

       

       

       

      HI

       

      thanks for getting back to me. I was unable to attach an excel file so i add to attach Screenshots,

       

      So if you see cust1 has a diffrent first date then cust2 so on for cust3 and 4

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 years ago

        Hi,

         

        Your question is not clear and neither is your data.  Paste a sample dataset, explain the business problem and show the expected result.

    • kdaya's avatar
      kdaya
      Icon for Helper I rankHelper I
      8 years ago

      PLease see each customer has a different first created Time..

       

      So i need to see the first 3 months of data for each customer.

       

      Does that make sense?

       

      Thanks

      • pxg08680's avatar
        pxg08680
        Icon for Resolver III rankResolver III
        8 years ago

        kdaya

        Try using the relative filtering. Drop your data into a table visual and make a slicer with customers.

        Select the table and in visual level filtering ,select relative filtering and use bottom 90 /top 90 accordingly.

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    kdaya

     

    You can create a YearMonth column and then rank the YearMonth for each user. So that we can filter the first 3 months for each user easily.

    For details, please refer to attached PBIX file.

     

    YearMonth =
YEAR ( Sheet1[Created Time] ) * 100
    + MONTH ( Sheet1[Created Time] )

    RankYearMonth = 
RANKX (
    FILTER ( Sheet1, Sheet1[Account] = EARLIER ( Sheet1[Account] ) ),
    Sheet1[YearMonth],
    ,
    ASC,
    DENSE
)

     

    Best Regards,
    Herbert

    • kdaya's avatar
      kdaya
      Icon for Helper I rankHelper I
      8 years ago

      thanks for your help, can you please tell me why you *100?

       

       

      • v-haibl-msft's avatar
        v-haibl-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        kdaya

         

        Just give a good format for the YearMonth column which data type is whole number, so that we can rank it correctly.

        We can also not *100, but in that case, YearMonth will be 2016 +5 = 2021 instead of 201600 + 5 = 201605.

         

        Best Regards,
        Herbert