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

Thanks Anonymous

 

I tried your solution but I ahve error with the first formula : previous date = CALCULATE(FIRSTNONBLANK(Table1[Date],Table1[Date]),FILTER(Table1,Table1[Customer id]=EARLIER(Table1[Customer id])&& Table1[Value]=EARLIER(Table1[Value])+1))

 

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

 

Moreover, I am not sure this will work because the "Value" column can have any value so the Table1[Value]=EARLIER(Table1[Value])+1 will not work.

 

My other question is, will this  solution work for creating a measure for each category. I need to be able to create something like a KPI for each category and having a measure "Category" won't allow me to di that, right ?

 

Thanks for your help.

 

Here is the link to my working version: https://1drv.ms/u/s!Ag5lje3-r6cXwRU7dXN9Bam7gLOG

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Anonymous,

I am not able to access the file you shared. In your scenario, you can add a index column in Query Editor, then right click your table and choose "New column" to apply the following DAX.


previous date = CALCULATE(FIRSTNONBLANK(Table1[Date],Table1[Date]),FILTER(Table1,Table1[Customer id]=EARLIER(Table1[Customer id])&& Table1[Index]=EARLIER(Table1[Index])+1))

Besides, what KPI would you like to create? Could you please post expected result in table format?

Regards,

Lydia

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Thank you Anonymous

     

    You should be able to download the file. I have it tested by someone and he was able to dl it and open it.

    Else try: https://www.justbeamit.com/aipfv

     

     

    Even if I add the index, I still have the problem with the earlier in the previous year formula "EARLIER/EARLIEST refers to an earlier row context which doesn't exist. " for both Cutomer Id and Index

     

    Regarding  the  KPI, imagine 3 cards visulization in power BI, each one for a category

     

    10                          20                      15

    Cat1                     Cat2                  Cat3

     

    Thanks

    • 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