Forum Discussion

Dimi_2207's avatar
Dimi_2207
Helper I
2 years ago
Solved

Serial number based on condition

Hello everyone!   Could, you, please, help me with the following task in DAX. I am attaching sample in excel. I have the following columns:   1) "Date" - Call date 2) Customer ID (can be custo...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    These calculated column formulas work

    Date of previous N = CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Customer Id]=EARLIER(Data[Customer Id])&&Data[Repeated calls in 7  Days ?]="N"&&Data[Date]<EARLIER(Data[Date])))
    Column = if(Data[Repeated calls in 7  Days ?]="N",1,CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer Id]=EARLIER(Data[Customer Id])&&Data[Date]>=EARLIER(Data[Date of previous N])&&Data[Date]<=EARLIER(Data[Date]))))

    Hope this helps.

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

These calculated column formulas work

Date of previous N = CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Customer Id]=EARLIER(Data[Customer Id])&&Data[Repeated calls in 7  Days ?]="N"&&Data[Date]<EARLIER(Data[Date])))
Column = if(Data[Repeated calls in 7  Days ?]="N",1,CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer Id]=EARLIER(Data[Customer Id])&&Data[Date]>=EARLIER(Data[Date of previous N])&&Data[Date]<=EARLIER(Data[Date]))))

Hope this helps.

 

Dimi_2207's avatar
Dimi_2207
Helper I
2 years ago

Thank you a lot for you help ! It worked perfectly

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    You are welcome.