Forum Discussion

shayla's avatar
shayla
Frequent Visitor
2 years ago
Solved

Calculated Column to Identify Whether Customer Purchased in Last Quarter

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

     

    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

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

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

    • shayla's avatar
      shayla
      Frequent Visitor
      2 years ago

      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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

     

    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