Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 have categorization rule I have to apply. Per exemple if the difference between its first activity in the period and the previous (which is not in the period) is 0 or 1 days, it is category 1. if it is between 2 and 5 days is is category 2. Else it is category 3.
I have a customer and date dimension, a fact table who track customer activity by date. Something like that
Customer id | Date | Value |
1 | 2017-11-30 | 1 |
2 | 2017-11-30 | 2 |
1 | 2017-11-29 | 3 |
1 | 2017-11-28 | 4 |
1 | 2017-11-27 | 5 |
1 | 2017-11-26 | 6 |
1 | 2017-11-22 | 7 |
1 | 2017-11-16 | 8 |
1 | 2017-11-01 | 9 |
I use the date from the date dimension like a slicer to select the period I want to analyse. So according the rule I explained:
if the period is between 2017-11-28 and 2017-11-30, I need measures telling me there is 1 Cat1 (customer #1), 0 in cat 2 and 1 Cat3 (Customer #2).
if the period is betwwen 2017-11-26 and 2017-11-28., the mesaures Cat 1 = 0, Cat 2 = 1 (Customer #1), Cat 3 = 0,....
I tried to apply SQLBI, Segmentation pattern, but it categorise me a customer in several category. A customer can be in only 1 category for the period selected.
To compute easily difference, I added a last actity column in the fact table where there is the date of the previous actity for the customer, I don't know if there is a good way to get this date using DAX.
I hope I am clear.
Solved! Go to Solution.
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,
When the period is between 2017-11-28 and 2017-11-30, the previous date for customer 1 is 2017-11-27, right? If so, please create the following columns in your fact table.
previous date = CALCULATE(FIRSTNONBLANK(Table1[Date],Table1[Date]),FILTER(Table1,Table1[Customer id]=EARLIER(Table1[Customer id])&& Table1[Value]=EARLIER(Table1[Value])+1))
Daysdiff = DATEDIFF(Table1[previous date],Table1[Date],DAY)
Then create the following measures in the fact table.
maxday per customer = MAX(Table1[Daysdiff])
Category = IF([maxday per customer]<>BLANK()&&([maxday per customer]= 0 || [maxday per customer]= 1), "Cat 1",IF([maxday per customer]<>BLANK() && [maxday per customer]>=2 && [maxday per customer]<=5,"Cat 2","Cat 3" ))
Regards,
Lydia
Thanks @v-yuezhe-msft
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,
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
Thank you @v-yuezhe-msft
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
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,
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
User | Count |
---|---|
93 | |
83 | |
77 | |
72 | |
65 |
User | Count |
---|---|
114 | |
101 | |
96 | |
65 | |
60 |