Not able to summarize for another date column

[aggkamakshi]

Hi Team,

I have 1 table i.e. 'report' which consist of 3 different types of date columns (date created, service date and paid date) , visits and rest other columns as well (some of which are part of slicers). I have then made date table, which is basically generating series by calculating min and max of date created columns and increaing date by 1. 

Futher I have established the relation by making date created relation as active (as it is playing major role in dashboard) and rest 2 relation i.e. service date and paid date as inactive. 

One to many relation is set up between date and report table. Date table is on one side and report table is on many side.

I like to calculate distinct count of visits month over month on service date then on paid date. My Measures from 1 to 4 are working well. However in measure 5 it is showing blank values. Can somebody please help why summarize is not able to perform well while calculating visits at paid date - for service date it is working well

(Below mentioned calculation is a sub part of main calculations, please help out through DAX only)

Measure 1

Trial File_name Visits =CALCULATE(

                                                 DISTINCTCOUNTNOBLANK(

                                                   report[Visit]

                                                   ),

                                                   report[File_name]="File_name"

                                       )

Measure 2

Trial File_Name Visits Paid = CALCULATE(

                                        [Trial file_name Visits],

                                        USERELATIONSHIP(

                                                   'Date Table'[Date_Created],

                                                    report[paid_date]

                                          )

                                      )

Measure 3

Trial file_name Visit SVC = CALCULATE(

                                            [Trial file_name Visits],

                                            USERELATIONSHIP(

                                                          'Date Table'[Date_Created],

                                                           report[svc_date_from]

                                             )

                                 )

Measure 4

Trail file_name Visits Numerator svc = AVERAGEX(

                                                       SUMMARIZE(

                                                                    report_,

                                                                     'Date Table'[Year Month],

                                                                      "#Visit",

                                                                     CALCULATE(

                                                                                 [Trial file_name Visit SVC], 

                                                                                  report[Flag]="Utilization",                     

                                                                                  report[File_name]="File_name"

                                                                      )

                                                           ),

                                                          [#Visit]

                                             )

Measure 5

Trail f ile_name Visits numerator paid = AVERAGEX(

                                                               SUMMARIZE(

                                                                            report,

                                                                            'Date Table'[Year Month],

                                                                            "#Visit",

                                                                             CALCULATE(

                                                                                   [Trial file_name Visits Paid],

                                                                                    report[Flag]="Utilization",

                                                                                    report[File_name]="File_name"

                                                                               )

                                                                 ),

                                                                 [#Visit]

                                                          )

Below is the table schema

''Report Table"

Date Created	Service Date	Paid Date	Visits	Rest few columns are part of slicer
2-Jan-18	5-Jan-18	4-Jan-18	dbehbdh4
3-Jan-18	6-Jan-18	8-Jan-18	sdgui78
5-Jan-18	13-Jan-18	9-Jan-18	wuisyw99
13-Jan-18	14-Jan-18	11-Jan-18	hdiuqedhowqlk0
17-Jan-18	16-Jan-18	12-Jan-18	bdswudgwu22
18-Jan-18	20-Jan-18	21-Jan-18	bdwiygi
27-Jan-18	29-Jan-18	22-Jan-18	wuisyw99
29-Jan-18	30-Jan-18	26-Jan-18	bdwy78
2-Feb-18	2-Feb-18	1-Feb-18	sj082nx
12-Feb-18	5-Feb-18	2-Feb-18	bdqwi789
15-Feb-18	6-Feb-18	7-Feb-18	bxywi9
19-Feb-18	10-Feb-18	8-Feb-18	dbwq8y87ebx
21-Feb-18	16-Feb-18	11-Feb-18	snwiu1te87
8-Mar-18	17-Feb-18	15-Feb-18	sn1iyi
9-Mar-18	18-Feb-18	21-Feb-18	nxue29u
12-Mar-18	25-Feb-18	22-Feb-18	xb1t78fyi
17-Mar-18	26-Feb-18	3-Mar-18	xnuo2d9
18-Mar-18	28-Feb-18	8-Mar-18	bc283u
25-Mar-18	5-Mar-18	11-Mar-18	sv8162xgeq
27-Mar-18	6-Mar-18	20-Mar-18	ddh68c
30-Mar-18	8-Mar-18	21-Mar-18	sbw9

"Date Table" i.e. Date = generateseries(min(report[date created]),max(report[date created]),1)

2-Jan-18	2018-1
3-Jan-18	2018-1
4-Jan-18	2018-1
5-Jan-18	2018-1
6-Jan-18	2018-1
7-Jan-18	2018-1
8-Jan-18	2018-1
9-Jan-18	2018-1
10-Jan-18	2018-1
11-Jan-18	2018-1
12-Jan-18	2018-1
13-Jan-18	2018-1
14-Jan-18	2018-1
15-Jan-18	2018-1
16-Jan-18	2018-1
17-Jan-18	2018-1
18-Jan-18	2018-1
19-Jan-18	2018-1
20-Jan-18	2018-1
21-Jan-18	2018-1
22-Jan-18	2018-1
23-Jan-18	2018-1
24-Jan-18	2018-1
25-Jan-18	2018-1
26-Jan-18	2018-1
27-Jan-18	2018-1
28-Jan-18	2018-1
29-Jan-18	2018-1
30-Jan-18	2018-1
31-Jan-18	2018-1
1-Feb-18	2018-2
2-Feb-18	2018-2
3-Feb-18	2018-2
4-Feb-18	2018-2
5-Feb-18	2018-2
6-Feb-18	2018-2
7-Feb-18	2018-2
8-Feb-18	2018-2
9-Feb-18	2018-2
10-Feb-18	2018-2
11-Feb-18	2018-2
12-Feb-18	2018-2
13-Feb-18	2018-2
14-Feb-18	2018-2
15-Feb-18	2018-2
16-Feb-18	2018-2
17-Feb-18	2018-2
18-Feb-18	2018-2
19-Feb-18	2018-2
20-Feb-18	2018-2
21-Feb-18	2018-2
22-Feb-18	2018-2
23-Feb-18	2018-2
24-Feb-18	2018-2
25-Feb-18	2018-2
26-Feb-18	2018-2
27-Feb-18	2018-2
28-Feb-18	2018-2
1-Mar-18	2018-3
2-Mar-18	2018-3
3-Mar-18	2018-3
4-Mar-18	2018-3
5-Mar-18	2018-3
6-Mar-18	2018-3
7-Mar-18	2018-3
8-Mar-18	2018-3
9-Mar-18	2018-3
10-Mar-18	2018-3
11-Mar-18	2018-3
12-Mar-18	2018-3
13-Mar-18	2018-3
14-Mar-18	2018-3
15-Mar-18	2018-3
16-Mar-18	2018-3
17-Mar-18	2018-3
18-Mar-18	2018-3
19-Mar-18	2018-3
20-Mar-18	2018-3
21-Mar-18	2018-3
22-Mar-18	2018-3
23-Mar-18	2018-3
24-Mar-18	2018-3
25-Mar-18	2018-3
26-Mar-18	2018-3
27-Mar-18	2018-3
28-Mar-18	2018-3
29-Mar-18	2018-3
30-Mar-18	2018-3

[Anonymous]

Hello, i am having the same problem, did you find the solution? thank you

[Anonymous]

Hi @aggkamakshi ,

In Measure 5, I found that you are referencing the Year Month column of the Date Table in the report table, which is unavailable.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aggkamakshi]

I have 2 tables, in my date table I have having year month column. For more understadnind please refer measure 4 which is working well, however things are not working with paid date

[lbendlin]

Your [Date] column in the Calendar table should not be named [Date_Created] - that is confusing.  

Can you confirm that in your visuals you use the [Date] column from the Calendar table?

[aggkamakshi]

I have 2 tables, date table is a generated series from min and max of date created further I have extracted year and month from the date created columns i.e. date table[date created] and date created[year month]