Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Segmentation

Hello,   I looking for a solution for the following problem. I need to be able to analyze data for a period based on the first occurence for the customer in the period and its previous activity. I ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    I think I finally found the solution by Adjusting Dynamic Segmentation pattern to my case:

    Cat = 
CALCULATE ( DISTINCTCOUNT(Customer[Id]),
FILTER ( ADDCOLUMNS ( Customer,"CustomerDiff",
CALCULATE ( FIRSTNONBLANK(TOPN ( 1, VALUES (FactTable[DiffInDays] ), FactTable[DiffInDays] ),1),
CALCULATETABLE ( Customer )
,ALLSELECTED ()
)
),
COUNTROWS (
FILTER (
'Cat', NOT(ISBLANK([CustomerDiff])) &&
[CustomerDiff] >= Cat[MinDiff]
&& [CustomerDiff] <= Cat[MaxDiff]
)
) > 0
)

     

    As I am not able to work with the earlier function I adjusted the formula: 

    Previous Date = 
var currentDate = FactTable[Date]
var currentCustomer = FactTable[Id]
VAR val = CALCULATE(MAX('FactTable'[Date]),
FILTER(ALL('FactTable'),
'FactTable'[Date] < currentDate
&& 'FactTable'[Id] = currentCustomer
)
)
return IF(ISBLANK(val),DATEVALUE("2017/01/01"),val)

    Is Earlier function better ?

     

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

I think I finally found the solution by Adjusting Dynamic Segmentation pattern to my case:

Cat = 
CALCULATE ( DISTINCTCOUNT(Customer[Id]),
FILTER ( ADDCOLUMNS ( Customer,"CustomerDiff",
CALCULATE ( FIRSTNONBLANK(TOPN ( 1, VALUES (FactTable[DiffInDays] ), FactTable[DiffInDays] ),1),
CALCULATETABLE ( Customer )
,ALLSELECTED ()
)
),
COUNTROWS (
FILTER (
'Cat', NOT(ISBLANK([CustomerDiff])) &&
[CustomerDiff] >= Cat[MinDiff]
&& [CustomerDiff] <= Cat[MaxDiff]
)
) > 0
)

 

As I am not able to work with the earlier function I adjusted the formula: 

Previous Date = 
var currentDate = FactTable[Date]
var currentCustomer = FactTable[Id]
VAR val = CALCULATE(MAX('FactTable'[Date]),
FILTER(ALL('FactTable'),
'FactTable'[Date] < currentDate
&& 'FactTable'[Id] = currentCustomer
)
)
return IF(ISBLANK(val),DATEVALUE("2017/01/01"),val)

Is Earlier function better ?

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Anonymous,

I can only reproduce your error when I create a measure using Earlier function. As my post, please create a calculate column, then apply the Earlier formula. 

In your scenario, as long as you get expected previous date value using new DAX formula, it is OK.

Regards,

Lydia