Calculated Column to Identify Whether Customer Purchased in Last Quarter

[shayla]

Hi There,

I want to insert a calculated column to see if customers did purchsed specific brand last quarter. 

I have tried the dax below, but as this customer didn't purchase Q4 2022, it should be no in the red square. 

Could anyone help?

Thanks,

Shayla

[v-yangliu-msft · Accepted solution]

Thanks for the reply from @Ashish_Mathur , please allow me to provide another insight: 

Hi  @shayla ,

From my understanding and your expected result is a bit different, [Customer ID=50004534&&[Date] = 2023.6.1,the most recent date is 2023.5.8, [Qtr & Year] is Q2 2023, not Q1 2023, so I'm showing a mismatch here

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
var _table=
CALENDAR(
    DATE(2021,1,1),
    DATE(2024,12,31))
return
ADDCOLUMNS(_table,"Qtr & Year","Q"&""&QUARTER([Date])&" "&YEAR([Date]))

2. Create calculated column.

Puchase Last Qtr=
var _date1=
MAXX(FILTER(ALL('Table'),
'Table'[Date]<EARLIER('Table'[Date])&&'Table'[Customer ID]=EARLIER('Table'[Customer ID])&&'Table'[Brand]=EARLIER('Table'[Brand])&&'Table'[Vol]<>0),[Date])
var _date2=
MINX(FILTER(ALL('Table 2'),'Table 2'[Qtr & Year]=EARLIER('Table'[Qtr & Year])),[Date])
var _enddate=
_date2-1
var _qu=MINX(FILTER(ALL('Table 2'),'Table 2'[Date]=_enddate),'Table 2'[Qtr & Year])
var _startdate=MINX(FILTER(ALL('Table 2'),'Table 2'[Qtr & Year]=_qu),[Date])
return
IF(
    _date1=BLANK(),BLANK(),
    IF(
       _date1>=_startdate&&_date1<=_enddate,"Yes","No"))

3. Result:

Best Regards,

Liu Yang

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

[Ashish_Mathur]

Hi,

Why are you looking at creating a calculated column formula?  Why not a measure?

[shayla]

Hi @Ashish_Mathur ,

As I need to calculate NetSales and Volume and number of customers for the customer whether purchased last quarter, so I want a calculated column. 

Thanks,

Shayla 

[Ashish_Mathur]

I still feel you should use a measure.

[shayla]

As I also need this to be in a slicer, could a measure be in a slicer?

could you suggest how could I do a formular in measure? 

Thanks,

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  For that dataset, show the exepcted result.