Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |