Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
duongan1602
Regular Visitor

Filter data from date condition

Hi

I have a table like this: 

DateCustomerSale
1-MarC200
5-AprA100
6-MayB200
8-JunC300
15-JunA100

 

I want to filter customer that bought in June but did not buy 2 month ago (May bought in March)

I've try this function but it not working

 

= FILTER (Table,
       AND
       Month(Table[Date])=6
       Calculate(Sum(Table[Sale]), Table[Date]<Date(2022,5,31) && Table[Date]>Date(2022,4,1)=BLANK())
)
 
I cannot understand how this works. Please help me.
4 REPLIES 4
isabella
Employee
Employee

Hi @duongan1602 

We can get this  in power query,my approach is to generate 2 tableone is  customerInJune the other is customerInPrevious2Monththen do a merge to filter out the customers InPrevious2Month.

 

1 .copy the original table ,then filter the date column like this:

 

isabella_0-1655878694338.png

 

 

isabella_1-1655878694341.png

 

 

2.copy  the original table ,then filter the date column like this:

 

isabella_2-1655878694343.png

 

 

isabella_3-1655878694343.png

 

 

 

Then merge the two table  , we want to filter out the customer in table customerInPrevious2Month

isabella_4-1655878694344.png

 

 

Now the merged  table is  looks like this :

isabella_5-1655878694345.png

 

 

 

We can remove the last column .

isabella_6-1655878694346.png

 

 

 

 

 

Best Regards,

Community Support Team _Isabella

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi, thank you for reply. I have read your PBI but not exactly what I need. 

I want to filter my table (export new table) to continue calculating data, so i need help to find a function to so my solution.

I do not understand what you want.  I have generated my result by writing a measure and have then used that meaure to filter the visual. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.