cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JAVED
Helper I
Helper I

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.

1 ACCEPTED SOLUTION

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

 

w4.PNG

 

Best Regards,
Qiuyun Yu 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-qiuyu-msft
Community Support
Community Support

Hi @JAVED,

 

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

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

 

w4.PNG

 

Best Regards,
Qiuyun Yu 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

I took it as a pie chart like 0-50,50-100,100-150 and i took the table with product name ,product code , price when i click on 0-50 in pie chart the table is not filtering it shows all values i want to see only 0-50 values.

Thanks for your help that works

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.