Date Table with weekday Table

[Mahmoud_nabir]

i have created date table and want to find the sum of planned visits during selected date

Date Table			Visits by WeekDay
Date	WeekDay			visitday	planned_visits
4/1/2018	Sunday			Friday	290
4/2/2018	Monday			Monday	1351
4/3/2018	Tuesday			Saturday	1411
4/4/2018	Wednesday			Sunday	1399
4/5/2018	Thursday			Thursday	1344
4/6/2018	Friday			Tuesday	1409
4/7/2018	Saturday			Wednesday	1363
4/8/2018	Sunday
4/9/2018	Monday

when select date from 4/1/2018  to 4/9/2018

i  need planned visit result as below

1399 + 1351 + 1344 +1363+ 1409+290+1411+ 1399 +1351

as i need sum planned visits without distinct by Visit day.

[Anonymous]

hi @Mahmoud_nabir

Try the following

a) Set a relationship between the Weekday of Date Table and VisitDay of VisitsPlanned table.

b) Set the relationship to Many to 1 and Crossfilter direction both

c)  Create a measure PlannedVistits= SUM([planned_Visits])

d) Create a slicer for Date from DateTable

e) Create a table visual with values as - Date from Date Table, Weekday from DateTable and measure [PlannedVisits]

f) You should get your result.

g) you could also show the [PlannedVisits] as a card visual.

Sample Screen Shot

If it works for you please accpet it as solution and also give KUDOS.

Cheers

CheenuSing

[Anonymous]

Hi @Mahmoud_nabir

I hope you have 2 tables Date Table, Visit by day table.

1. Make sure you form the relationship between these 2 tables using the Date.Weekday= VisitbyDay.VisitDay columns , it should be M:1 ( Many end of date, 1 end of Visitday).

2. Pull the date ( from data table ) and SUM(visitbyday0 Planned_Visits])  into your visual, you will get the desired result.

I hope this is what you are looking for.Let me know if you face any issues.

Thanks
Raj

[Mahmoud_nabir]

Dear rajendran

thanks for your replay.

unfortunately it doesn't work as it result the planned visits for one week only.