How to create a column to filter date_first_buy > 2020 Jan 1 ?

[admin11]

Hi All

I like to filter those customer first buy date from 1 Jan 2020 . i have try :-

SHOW_NEW_ACC_15_MTH_AGO = IF ('Date'[DATE_FIRST_BUY] >= EDATE(TODAY(),-15), 1, 0)

it does not work , hope some one can share wth me. ( No error msg)

My PBI file :-

https://www.dropbox.com/s/b42p11do7zhw0mf/PBT_V2021_392%20TI_SI_GLhow%20to%20filter%20those%20first%...

Paul

[v-robertq-msft · Accepted solution]

Hi, @admin11 

According to your description and sample picture, it seems that the [date_first_buy] is a measure, you can’t create a calculated column to quoting other measures, so you can only achieve this using measure, you can try this measure:

Flag =

IF([date_first_buy]>=DATE(2021,1,1),1,0)

Then apply the filter of this table chart like this, because measure can’t be placed into the slicer, you can only use filter to achieve this:

And you can get what you want, your pbix file is too large for me to open, so I just created some test data to do some test.

You can download my test pbix file here

If this result is not what you want, you can post your pbix file with some test data(without sensitive data) so that I can open it and test it in your pbix file.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[admin11]

@v-robertq-msft 

Thank you very much

[Jihwan_Kim]

Hi, @admin11 

I am not sure if I understood correctly, but please try the below.

VAR firstbuy =

CALCULATE(MIN(SALES[date]),DATESBETWEEN('Date'[Date],MINX(ALL('Date'),'Date'[Date]),MAX('Date'[Date])))

RETURN

IF( firstbuy < date(2020,1,1), BLANK(), firstbuy)

it shows blank if it is before 2020.

Or, if you want to filter the customers by the slicer, I only know one way and that is to create a new column that indicates the first buy date inside the customer table.

Jihwan Kim

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

[admin11]

@Jihwan_Kim 

Thank you for sharing , i try to apply the syntax , it got error below . i am unable to clear error . hope you can advise me.

[Jihwan_Kim]

Sorry, I did not copy-paste my measure from pbix to here correctly

please try below.

First_Day_Buy =

VAR firstbuy =

CALCULATE(MIN(SALES[date]),DATESBETWEEN('Date'[Date],MINX(ALL('Date'),'Date'[Date]),MAX('Date'[Date])))

RETURN

IF( firstbuy < date(2020,1,1), BLANK(), firstbuy)

[admin11]

@Jihwan_Kim 

I try your code , still cannot work. Actually i need to create column , apply at Slicer , to filter those new customer start buy since 1 jan 2020.

Paul

[jppv20]

Try this:

SHOW_NEW_ACC_15_MTH_AGO = IF ('Date'[DATE_FIRST_BUY] >= DATE(2020,01,01), 1, 0)

[amitchandak]

@admin11 , Try a new column

flag = IF ('Date'[DATE_FIRST_BUY] >= date(2020,1,1), 1, 0)

[admin11]

@amitchandak 

@jppv20 

Thank you very much for both of sharing. unfortunately both syntax not able retrun 1 and 0 , it return 0 and 0. 

May be due to my date with time stamp , include the hr min sec . that why it cannot work ? see below image.

[jppv20]

@admin11 
Try a calculated column for Date_First_Buy, then try a new column with IF ('Date'[DATE_FIRST_BUY] >= date(2020,1,1), 1, 0)

[admin11]

@jppv20 

When i try to create column for Date_First_Buy , My table data become messy.  So i cannot go further next step. as not easy for me figure out which are those new customer ?

[jppv20]

@admin11  Maybe this will help:

Date_First_Buy_ = Calculate(Min(Sales[Date]),Allexcept(Customertable,Customertable[Customer Name])