Forum Discussion

JAVED's avatar
JAVED
Helper I
7 years ago
Solved

Filtering

Hi 

I have a 3 column with Consumed ,Date,Product Name

Now based on last 6 months i need to divide the product column in to 3 one should show only first 50 Products consumed on the last 6 months next column should show only from 50 to 150, 3rd coloumn should show only150 to 200 or remaining 

How can i make it, i tried using rank function on date but i am not able to divide it in to 3 limited columns like 1 to 50

 

Any help appreciated.

APIS and Embedding
Content Packs
Custom Visuals
Data Modeling
Gateways
Mobile
Reports
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    7 years ago

    Hi JAVED,

     

    You can create calculated columns below: 

     

    0-50 = IF(DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)<=50,Table1[Price],BLANK())

     

    50-150 = IF(DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)<=150 && DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)>50,Table1[Price],BLANK())

     

    >150 = IF(DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)>150,Table1[Price],BLANK())

     

     

    Best Regards,
    Qiuyun Yu 

     

5 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    7 years ago

    Hi JAVED,

     

    Would you please share some sample data and clarify corresponding desired results? 

     

    Best Regards,
    Qiuyun Yu 

    • JAVED's avatar
      JAVED
      Helper I
      7 years ago
      Part No.Part NamePriceDate
          
      ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68November 07, 2017
      ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68November 07, 2017
      ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68October 17, 2017
      ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68October 15, 2017
      ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68October 14, 2017
      ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68October 12, 2017
      ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68October 06, 2017
      ISO-PRIMER-PR1K ISOLATING  PR  GREY316.68September 19, 2017

       

      Dis is sample data,here i have 10 years of data but i need to display only 6 months data. i need to create 3 columns one should display only 1 to 50 days price of part name 2nd column as 50 to 150 days price of partname remaining as one column.Here i gave ranks to date but i am not able to divide the column in to 3 as desired

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Community Support
        7 years ago

        Hi JAVED,

         

        You can create calculated columns below: 

         

        0-50 = IF(DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)<=50,Table1[Price],BLANK())

         

        50-150 = IF(DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)<=150 && DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)>50,Table1[Price],BLANK())

         

        >150 = IF(DATEDIFF('Table1'[Date],MAX('Table1'[Date]),DAY)>150,Table1[Price],BLANK())

         

         

        Best Regards,
        Qiuyun Yu