Calculate Sum in one table based on dates in other tables.

[Vatz8]

Hi,

I have three Tables.

Table A

ID|       Assesment_Date                   | Stay Start  Date           | Stay End Date    
--------------------------------------------
1001 | 2019-03-04| 2019-03-04 | 2019-03-07 
--------------------------------------------
1001 | 2019-03-09 | 2019-03-09 | 2019-03-12 
--------------------------------------------
1000| 2019-03-09| 2019-03-05  | 2019-03-010 
--------------------------------------------
1000 | 2019-05-07 | 2019-04-05  | 2019-06-04 

Table B

ID|       Census_Date                   |Reimbursement
--------------------------------------------
1001 | 2019-03-04|  $500
--------------------------------------------
1001 | 2019-03-05| $500
--------------------------------------------
1000| 2019-03-09|$2000
--------------------------------------------
1000 | 2019-05-07 |$5000

Table C is a Calendar Table. This table is used as a date slicer in the final report. 

Currently Calendar Table is connected to Table A on Assement date from Table A and Date from calendar table.

Table A and Table B are connected on ID. Please note that is is a many-many relationship as there can be multiple Assesment dates for a single ID in table A and there can be multiple reimbursement for one ID.

Right now the formula I am using to calculate the sum is-

Stay Reimbursment1 = CALCULATE(SUM('TableB'[DailyReimbursement]),USERELATIONSHIP('Table B'[CensusDate],'Calendar table'[Date]))

This gives corrent output only when I select correct stay start and stay end date in the slicer for all individual assesments. If i select a wider date range which might have more assesment dates in it the calculation is wrong and shows the total sum based on the whole date range slicer for all records .Example of output i currently get right now.

Output xample-

ID|       Assesment_Date                   | Stay Start  Date           | Stay End Date     | Total Sum
--------------------------------------------
1001 | 2019-03-04| 2019-03-04 | 2019-03-07 | $1000
--------------------------------------------
1001 | 2019-03-09 | 2019-03-09 | 2019-03-12 |$1000
--------------------------------------------
1000| 2019-03-09| 2019-03-05  | 2019-03-010 |$7000
--------------------------------------------
1000 | 2019-05-07 | 2019-04-05  | 2019-06-04 |$7000

Here the output 1001 | 2019-03-04| 2019-03-04 | 2019-03-07 | $1000 is correct for this date range but not correct for the second row.

So the my requirement is as follows-

Calculate the Sum based on the date Slicer(Calendar Table) but also Stay Start and Stay End. By this What I mean is that-

1)If Stay start date is in slicer date and stay end date is outside the slicer , then calculate the sum from Stay start date to the slicer end range date. 

2) If stay end date is in slicer date and stat start is sutside the slicer, then calculate sum from the slicer start range date to the Stay end date.

3) If the stay start and stay end dates are both inside the slicer date range then calculate the sum from stay start to stay end date.

I dont know if I am creating the wrong relationships or not but I need help as there are a lot of dates involved.

[v-yangliu-msft]

Hi  @Vatz8 ,

You can try the following measure

Measure =
var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date])
var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date])
var _tableAstart=MAX('TableA'[Stay Start Date])
var _tableAEnd=MAX('TableA'[Stay End Date])
return
SWITCH(
    TRUE(),
_tableAstart>=_mindate&&_tableAEnd>_maxdate,
SUMX(FILTER(ALL(TableB),'TableB'[ID]=MAX('TableA'[ID])&&'TableB'[Census_Date]>=_tableAstart&&'TableB'[Census_Date]<=_maxdate),[Reimbursement]),
_tableAstart<_mindate&&_tableAEnd<=_maxdate,
SUMX(FILTER(ALL(TableB),'TableB'[ID]=MAX('TableA'[ID])&&'TableB'[Census_Date]>=_mindate&&'TableB'[Census_Date]<=_tableAEnd),[Reimbursement]),
_tableAstart>=_mindate&&_tableAEnd<=_maxdate,
SUMX(FILTER(ALL(TableB),'TableB'[ID]=MAX('TableA'[ID])&&'TableB'[Census_Date]>=_tableAstart&&'TableB'[Census_Date]<=_tableAEnd),[Reimbursement]))

Best Regards,

Liu Yang

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

[Vatz8]

Hi,

This is returning no values. I think this is because the relation between Table A and Table B is on ID. And the Sumx filter tables has comparisons between Census_date and stay start , stay end dates. The relationships are as follows-

Table A is connected to Calendar table via Assesment Date(This date acts as a unique ID). Table A and Table B are connected via ID. This is a many to many relationship.

Table B and Calendar date cannot be directly connected on date because this creates an error. So it has an inactive relationship which gets activated when I use userelationship.Please advise me if I need to change relationships around to make this work.