calculate among 3 tables using 2 time series

[BusinessAnalyst]

Dear experts,

I am struggling with the calculation among 3 relationship-based tables, connected by ID, resourced from a SQL online database, as below:

- Table 'All customers' contains ID of all customer IDs uniquely.

- Table 'All sales' records every purchase a customer makes. One customer can purchase several times while others don't. So this table only contains the one that purchase only. For example: 

ID      PurchaseDay           TotalAmount

01     25/10/2016             $100

02     25/10/2016             $112

02     26/10/2016             $32

- Table 'RegistrationDate' records all customers registration time, count on week number in year. For example: 

ID      Week Number of RegistrationDate

01      22_2016    (means: ID 01 registrated in week 22 year 2016)

02      22_2016

03      23_2016

Please take a look at the SAMPLE DATA

My idea is to create a calculation table that shows:

ID	Week number of registration	Week number of observation	Gap time (calculated by: Week number of observation - Week number of registration)	TotalAmount ($)
1	201612 (means: week 12 year 2016)	201630 (means: week 30 year 2016)	18 (means: 201630 - 201612 = 18)	100
2	201614	201630	16	123
3	201617	201630	13	0
4	201617	201625	8	234
1	201612	201612	0

To achieve that, I suppose a date table or a table of unique value of week number (for instance, week 01_2016 til week 52_2016) is needed. I made them but can't link them to make the calculation above work correctly.

I'd like to ask for your help. Many thanks in advance! 

With kind regards,

Cindy

[BhaveshPatel]

HI CINDY,

I have used query editor for solving your problem. 

Step 1: Registrations Date Table

Firstly, I have created a duplicate column of week number of registration.

I have split the column by delimiter " _ " .

I have renamed both columns and changed the data type to Text.

after that, I have created a custom column and joined both columns using " & " and changed the data type "Whole Number".

Split ColumnChanged the data type to TEXTCustom ColumnChanged the data type to Whole Number

Step 2:

Creating a new merged table of Registration Date and All Purchases.

Go to Registration Date Table and Select Merge Queries as New.

Merge by common field ID AND select left outer join

EXPAND the newly created column, Select only Week number of Purchase Day and Total Amount.

Create new custom column GAP TIME as shown in screenshot.

Rename the both columns.

MERGE QUERIES(REGISTRATIONS DATE AND ALL PURCHASES)EXPAND COLUMN, SELECT WEEK NUMBER OF PURCHASES AND TOTAL AMOUNTGAP TIME CUSTOM COLUMN

You can download the sample file at HERE.

FINAL RESULTS

[BusinessAnalyst]

Dear Bhavesh and other experts,

I am grateful for your help very much, however the result only shows IDs which purchased, while my wish is to create a table in which all IDs, either purchased or not, are showed. If some ID doesn't purchase in a specific week of observation, the amount returns to 0 (zero). That's why I assume another table of 'week no. of observation' that runs from 201601 to 201653 (count for this year only) is necessary. Please see the expected table result below: 

ID			Week no. Of registration			Week no. Of observation			Gap time			Amount
1			201612			201612			0			0
1			201612			201613			1			0
1			201612			201614			2			0
1			201612			201615			3			124
1			201612			201616			4			213

I look forward to your helps!

Regards,

Cindy

[v-qiuyu-msft]

Hi @BusinessAnalyst,

As suggested by @BhaveshPatel, you can merge "RegistrationDate" table and "All Purchase" table use Left Out Join, it will return all the ID whether this user purchase or not.

In your scenario, the issue can be that you haven't allow "Show items with no data" in a table visual. Please enable this option. Also you can create a calculated column like below to return TotalAmount based whether the ID purchase or not.

Amount = IF('Merge1'[PurchaseDay] =blank(),0,'Merge1'[TotalAmount])

Then place this column into the table. You can refer to attached .PBIX file.

Best Regards,

Qiuyun Yu

[BusinessAnalyst]

Dear @v-qiuyu-msft and @BhaveshPatel,

I appreciate very much your time to work on the above case. My idea was to check randomly a "week number", for example, this week week 43, 2016 to see:

- How long does it take from "Week number of Registration" to "week 43, 2016" (which is called "Gap time"). If an ID registed in week 20 year 2016, and it does't have a purchase in week 43 year 2016, "Gap time" should always appear as 23 (week 43 -week 20 = 23 weeks). In your work above, now it turns out blank and we only can calculate "Gap time" when an ID has purchase. 

Additionaly, I created a column to classify type of purchase, based on how much (Purchase) amount: type A, B, C. I want to calculate the number of each type with filter of "Week number of Registration" and "Week number". For example, I used: Type A = CALCULATE(count[ID],FILTER('Merge1',[Type]="Type A")), but it turned out blank. Could you please take a look at the file and give me some insights to do it correctly.

Sample data.v2

I enjoy learning from you.

With kind regards,

Cindy

[v-qiuyu-msft]

---

- How long does it take from "Week number of Registration" to "week 43, 2016" (which is called "Gap time"). If an ID registed in week 20 year 2016, and it does't have a purchase in week 43 year 2016, "Gap time" should always appear as 23 (week 43 -week 20 = 23 weeks). In your work above, now it turns out blank and we only can calculate "Gap time" when an ID has purchase. 

---

When the ID doesn't have purchase, both "Week Number" and "TotalAmount" are blank. So if you place "Week Number" in a slicer and the user checks one week number to filter the table, the "Week Number" display in the table are always same as the one checked in the slicer. If you want to calculate the "Gap time", you can create a calculated column like this:

GapTime = Merge1[Week number]-VALUE(LEFT('Merge1'[Week Number of Registration],2))

---

I appreciate very much your time to work on the above case. My idea was to check randomly a "week number", for example,

Additionaly, I created a column to classify type of purchase, based on how much (Purchase) amount: type A, B, C. I want to calculate the number of each type with filter of "Week number of Registration" and "Week number". For example, I used: Type A = CALCULATE(count[ID],FILTER('Merge1',[Type]="Type A")), but it turned out blank. Could you please take a look at the file and give me some insights to do it correctly.

---

I have tried the measure and it can return values in a card visual:

Type A = CALCULATE(COUNT('Merge1'[ID]),FILTER('Merge1',[Type]="Type A"))

Best Regards,
Qiuyun Yu

[BusinessAnalyst]

Hi @v-qiuyu-msft,

First of all,

"Type" is calculated based on weekly basic.

SalesAmount = CALCULATE(sum(Merge1[Amount]),filter('Merge1',Merge1[YearWeeknum of PurchaseDay]))

Type = if([SalesAmount]=blank(),blank(),if([SalesAmount]<1000,"Type A",if([SalesAmount]<4000, "Type B", "Type C")))

So if we mark Week number, Week number of Registration, and choose "sum" in Amount, as below, the number "Type A" in visual card turn out incorrectly. There are only 5 of Type A on this visual but it shows 227.

Secondly, is it  any way to create a slicer for "Type"? it is a measure. How to adjust the formula to make it possible for slicer?

Thirdly, the visual I wish to see is:

ID			Week number of registration				Week number			Type			Amount			Gap time
1			01_2016				1			Type A			0			0
2			01_2016				1			Type A			0			0
3			01_2016				1			Type A			0			0
4			01_2016				1			Type A			0			0
5			01_2016				1			Type A			0			0
1			01_2016				2			Type A			0			1
2			01_2016				2			Type A			0			1
3			01_2016				2			Type B			2000			1
4			01_2016				2			Type C			5000			1
5			01_2016				2			Type C			7000			1

For example all ID customers that registered in Week 01_2016 are 1, 2, 3, 4, 5. I wish the Week number column also shows from 1, even there is no purchase at all. Then in week number 2, ID 3, 4, 5 had purchases, and ID 1, 2 did not, but all 5 should apprear on the visual. It means Week number column is not taken from PurchaseDay, suggesting there may need a date table?

With kind regards,

Cindy

[v-qiuyu-msft · Accepted solution]

Hi @BusinessAnalyst,

Please create a new table use DAX:

Table = SUMMARIZE('Merge1',Merge1[ID],Merge1[RegistWeek],Merge1[Week number],"TotalAmount",sum('Merge1'[Amount]))

Then create a column to return type:

Type01 = if([TotalAmount]=blank(),blank(),if([TotalAmount]<1000,"Type A",if([TotalAmount]<4000, "Type B", "Type C")))

Create a measure to return count of Type A:

CountOfTypeA = SUMX('Table',IF([TotalAmount]<1000,1,0))

We are not able to add a measure as slicer values, please drag teh calculated column Type01 to the slicer.

Best Regards,
Qiuyun Yu

[BhaveshPatel]

@v-qiuyu-msft Thank you for the clarification. Much Appreciated!