Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bcosta98
Helper I
Helper I

Filtering Sales between Notification Dispatch and Sales Time

Hi, I am having trouble trying to visualize the effects of the notification on Sales.

I have three tables of content:

One which describes the Notification and have the ID and the Dispatch time

One which has the NotificationID, UserID and Dispatch Status (this is if the user received or not)

One which I have Sales information including UserID and time

 

I would like to see the impact of number of sales and amount spent by the diference between the dispatch time and sale time. Also I would like to compare the sales between users that received the Notification or Not. Like how many users that received bought something and how many that did not received bought in the same time period.

 

For that I would like to Filter the sales by diferent diferences in time.

 

I am not an expert in Power Bi, actually I am pretty new to it. So I don't actually know many power query tools

 

Thank you

 

1 ACCEPTED SOLUTION

Nice.

 

A couple of things. I suspect that these sample data are simplified, perhaps to much. As long as each customer only has 1 notification, the relationships are 1-to-many. But once you have a customer with more than 1 notification the relationships between the tables will be many-to-many. Many-to-many relationships are much more complex to handle than 1-to-many.

 

You wrote that you want to analyse on the difference in time between dispatch and sales. How should the difference be calculated when there are more than 1 sale? 

 

You were also looking for the difference between sales to customers who received notification to those that don't. But your sample sales data does not contain any customers who did not reveice notification.

What you could do is to do some calculations on your sample data in Excel to describe you desired output.

If you are having problem sharing the data directly in the forum, you can upload the data to dropbox/onedrive/other, and share the link

View solution in original post

7 REPLIES 7
sturlaws
Resident Rockstar
Resident Rockstar

Hi @bcosta98,

 

your question is well written, but it will be much easier to help you if you add some sample data, or a sample report.

 

Cheers,
Sturla

 

Hi @sturlaws 

This are the tables

NotificationNotification

SalesSales

Notification_UserNotification_User

 And the relations goes as follows:
Notification 1:* Notification_User

Notification_User *:1 User

User 1:* Sales

 

Sorry I haven't uploaded the corect picture for Notification_User table

WhatsApp Image 2020-04-23 at 6.19.10 PM.jpeg

 

Here it is @sturlaws 

Could you attach your sample data as file(s)? It will save me the time of typing all the data.

Hi @sturlaws I really cound't attach. I am Writting an example

 

NotificationIDDate_ScheduleDateReadDate_Dispatchappid
1232020-01-27 10:45:002020-02-26 17:20:122020-01-27 10:59:226
1732020-02-10 15:30:002020-02-26 17:20:122020-02-10 15:42:316
7332020-04-17 18:30:002020-04-17 18:30:232020-04-17 18:34:426
7342020-04-19 18:30:002020-04-19 18:30:502020-04-19 18:31:336
7352020-04-19 18:30:002020-04-19 18:30:532020-04-19 18:31:506

 

 

NotificationIDUserIDStatusID
123921321
123952591
1231071701
173791371
173791941
173792861
733766981
733777671
733779301
733779391
733781201
7352562921
7352567741
7352577021
1232305712
123977222
173794782
733777800
733793960
734789770
734792750
734797360
734929300
734985860

 

SaleIDvalueDiscountDateComandIDUserIDTypeID
199416 27/01/2020 05:3165031071702
188135 27/01/2020 01:236285791371
187766 27/01/2020 19:476278792862
187915 28/01/2020 19:586280791372
187635 27/01/2020 04:256273791372
168655,55 28/01/2020 06:285969921322
163131,58 28/01/2020 02:435881766983
160529,25 28/01/2020 08:405846791942
160824,9 28/01/2020 23:095854791942
166120 28/01/2020 00:565931792862
184019 28/01/2020 16:336212921322
1596125,4 28/01/2020 07:595834779302
1581100 27/01/2020 10:335799779301
1580124,3 28/01/2020 00:225797791372
156920 27/01/2020 02:555782792862
161920 27/01/2020 11:5758681071702
169922,9 28/01/2020 21:255995777672
185322,9 01/01/2020 20:516230792862
157348 01/01/2020 22:095787791943
155352,45 01/01/2020 18:175763791371
155012 10/01/2020 14:435760791373
15525,5 23/02/2020 12:185762791943
189622,9 06/02/2020 12:2363171071703
154741,8 20/02/2020 21:325747766982
154636,3 20/02/2020 14:305743791943
154535,2 20/02/2020 14:295743791943
154061 20/02/2020 12:245736952592
166016,9 07/02/2020 12:055928791372
169520,9 13/02/2020 12:085991766982
185117,9 29/02/2020 12:216229792862
154426,4 20/02/2020 13:325740952592
160916,9 31/03/2020 13:005855791942
167216,9 08/04/2020 13:435944792862
172517,9 17/04/2020 13:2560311071702
152752,8 19/04/2020 13:335711792863
154117,9 20/05/2020 12:3157371071701
151229,7 19/04/2020 19:115694777672
151137,4 19/04/2020 19:115694921322
150214,9 18/04/2020 12:0156811071703
148326 19/04/2020 21:195654791373
164926 19/04/2020 20:095912791943
175126 17/04/2020 18:246070791943
188026 20/04/2020 14:556284952593
144820 17/04/2020 09:245578791372
144318,9 18/04/2020 20:085572777672
144617,9 20/04/2020 19:565575792862
147316,9 20/04/2020 17:375628791372
156820,9 20/04/2020 18:4457811071702
162816,9 18/04/2020 08:145880792862
14945,5 19/04/2020 09:205673766982
149617,5 19/04/2020 02:195675791372
153510,25 17/04/2020 13:325728791372
153722,9 18/04/2020 19:465730952592
15488,25 18/04/2020 08:135758952592
15543 20/04/2020 12:575764777672
156220 17/04/2020 23:0757721071702
15679 20/04/2020 13:175780792862
15769 20/04/2020 23:135790791372
160236,8 20/04/2020 10:555843777672
16037,5 17/04/2020 04:375844921322
16256,5 17/04/2020 19:245877921322
163317,5 18/04/2020 16:0058861071702
16348,5 20/04/2020 01:015887791942
165511,5 19/04/2020 01:335923921322
165615,5 17/04/2020 09:245924792862
16646 18/04/2020 04:395934777672
16823 19/04/2020 05:195960791372
17023 20/04/2020 14:375998779392
170814 20/04/2020 15:3860061071702
171147,812,520/04/2020 10:476013921322
17276 17/04/2020 20:1160322567742
17409,5 20/04/2020 14:306048766982
176247,8 20/04/2020 06:446094791942
177912 17/04/2020 03:596113921322
17874,5 17/04/2020 22:1961231071702
180917,51017/04/2020 08:266151791372
18173 20/04/2020 02:486164791942
182814,5 18/04/2020 03:556195792862
185716,5 17/04/2020 21:416239791372
186427317/04/2020 11:276248766982
186918,5 20/04/2020 10:596254952592
188953,8 18/04/2020 07:406300779302
19063,5 19/04/2020 04:166341792862
192721,5 18/04/2020 21:396385779392
193447,84,7820/04/2020 15:1463901071702
19883 17/04/2020 13:266489792862
199647,8 17/04/2020 15:276509791372
154216,9 18/04/2020 03:175738791942
144534,1 19/04/2020 04:275573921323
148935,755,618/04/2020 11:005664952593
151931,9 18/04/2020 03:585701792863
162131,9 19/04/2020 16:2158711071703
139720 18/04/2020 00:445490792862
141221,9 17/04/2020 08:205524952592
14278,25 18/04/2020 14:315552791942
14356,75 19/04/2020 09:105560791372
1464201217/04/2020 23:085606792862
150527,25 18/04/2020 12:305687791372
156320 19/04/2020 06:405773779302
166220 19/04/2020 10:1059321071702
167120 20/04/2020 02:115943766982
170127,9 20/04/2020 08:395997791372
171322,9 20/04/2020 07:026016791942
175624,91220/04/2020 07:176087921322
186326,9 20/04/2020 19:186247952592
141016,9 19/04/2020 00:3055161071702
152615,9 17/04/2020 05:515713952592
138720,9 18/04/2020 04:145463952592
14201 18/04/2020 22:295545952592
164150 19/04/2020 00:295892766982
16651 20/04/2020 13:5359351071702
16681 20/04/2020 13:105937777672
16790,25 17/04/2020 09:5259521071702
16800,25 18/04/2020 19:215953792862
16810,25 17/04/2020 15:225954791372
16881 20/04/2020 01:515972779302
17095,5 11/02/2020 20:236007766982
17151 10/02/2020 03:286018952592
17191 10/02/2020 22:5860221071702
17551,5112/02/2020 02:426085791372
17661 10/02/2020 10:146101921322
137824,9 11/02/2020 04:575444777673
146324,9 10/02/2020 02:065604792862
166920,9 12/02/2020 14:545940791942
171226,91012/02/2020 23:536015791372
15156,5212/02/2020 17:1356971071703
157768 11/02/2020 17:535802777672

Nice.

 

A couple of things. I suspect that these sample data are simplified, perhaps to much. As long as each customer only has 1 notification, the relationships are 1-to-many. But once you have a customer with more than 1 notification the relationships between the tables will be many-to-many. Many-to-many relationships are much more complex to handle than 1-to-many.

 

You wrote that you want to analyse on the difference in time between dispatch and sales. How should the difference be calculated when there are more than 1 sale? 

 

You were also looking for the difference between sales to customers who received notification to those that don't. But your sample sales data does not contain any customers who did not reveice notification.

What you could do is to do some calculations on your sample data in Excel to describe you desired output.

If you are having problem sharing the data directly in the forum, you can upload the data to dropbox/onedrive/other, and share the link

Hi @bcosta98 ,

 

To create relationship between tables should help you in your scenario.

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.