Sum values of given period with DATESBETWEEN

[zyc]

Hi. I have the following 2 tables:

Table 1 - breakdown of daily value

ID	event-time	Value
1	1-Jan-21	1.5
1	2-Jan-21	1.8
1	4-Jan-21	9.0
1	6-Jan-21	32.2
1	7-Jan-21	3.4
1	8-Jan-21	11.0
1	9-Jan-21	8.0
1	13-Jan-21	14.0
1	14-Jan-21	9.0
1	15-Jan-21	1.0
2	7-Jan-21	6.0
2	8-Jan-21	5.0
2	9-Jan-21	10.0
2	10-Jan-21	13.0
2	11-Jan-21	2.0

Table 2: overview with event ID

ID	event ID	start-date	end-date	Desired Output
1	1.1	31-Dec-20	4-Jan-21	12.3
1	1.2	5-Jan-21	10-Jan-21	54.6
1	1.3	12-Jan-21	17-Jan-21	24
2	2.1	6-Jan-21	12-Jan-21	36

Essentially I have a master table (table 2) with ID, event IDs, end and start dates. I would ideally like to "sumif" table 1 if ID matches, and if the event-time is => start-date, and =< end-date. 

However, I am having issues creating a measure for it. I tried the below but it shows an error:

DesiredOutput = CALCULATE (SUM(Table1[Value]), Table1[ID], Table2[event ID], DATESBETWEEN (Table1[event-time], MAX(Table2[start-date]), MAX(Table2[end-date])))

MdxScript(Model)(35,173)Calculation error in measure 'Table2[DesiredOutput]': A date column containing duplicate dates was specified in the call to function 'DATESBETWEEN'. This is not supported. 

[CNENFRNL · Accepted solution]

DesiredOutput = 
CALCULATE(
    SUM( BREAKDOWN[Value] ),
    FILTER( BREAKDOWN, BREAKDOWN[ID] = OVERVIEW[ID] ),
    DATESBETWEEN( BREAKDOWN[event-time], OVERVIEW[start-date], OVERVIEW[end-date] )
)

I prefer

TOTAL = 
VAR __id = OVERVIEW[ID]
VAR __start = OVERVIEW[start-date]
VAR __end = OVERVIEW[end-date]
RETURN
    SUMX(
        FILTER(
            BREAKDOWN,
            BREAKDOWN[ID] = __id
                && BREAKDOWN[event-time] >= __start
                && BREAKDOWN[event-time] <= __end
        ),
        BREAKDOWN[Value]
    )

[zyc]

@CNENFRNL 

I had changed all the data types similarly to how you had them. 

Only difference here is that I had to add max() when defining the variables for it to be working for me. Might the difference in syntax be due to the different PowerBI desktop versions?

TOTAL = 
VAR __id = MAX(OVERVIEW[ID])
VAR __start = MAX(OVERVIEW[start-date])
VAR __end = MAX(OVERVIEW[end-date])

[zyc · Accepted solution]

@CNENFRNL thank you so much for the above. The file that you sent over works.

I tried to replicate on my end but I have this error "A single value for column 'ID' in table 'OVERVIEW' cannot be determined. THis can happen when a measure formula refers to a column that contains many values without specifiying an aggregation such as min, max, count, or sum to get a single result."

[CNENFRNL · Accepted solution]

DesiredOutput = 
CALCULATE(
    SUM( BREAKDOWN[Value] ),
    FILTER( BREAKDOWN, BREAKDOWN[ID] = OVERVIEW[ID] ),
    DATESBETWEEN( BREAKDOWN[event-time], OVERVIEW[start-date], OVERVIEW[end-date] )
)

I prefer

TOTAL = 
VAR __id = OVERVIEW[ID]
VAR __start = OVERVIEW[start-date]
VAR __end = OVERVIEW[end-date]
RETURN
    SUMX(
        FILTER(
            BREAKDOWN,
            BREAKDOWN[ID] = __id
                && BREAKDOWN[event-time] >= __start
                && BREAKDOWN[event-time] <= __end
        ),
        BREAKDOWN[Value]
    )

[zyc]

I now have a new requirement. I have successfully managed to obtained the "TOTAL". However, I have to multiply them by a factor specific to its category.  I suspect that this is a SUMPRODUCT problem but have yet to modify the previous solution provided to achieve it. 

OVERALL:

ID	event ID	start-date	end-date
1	1.1	2020-12-31	2021-01-04
1	1.2	2021-01-05	2021-01-10
1	1.3	2021-01-12	2021-01-17
2	2.1	2021-01-06	2021-01-12

BREAKDOWN: the difference between this breakdown table and previously mentioned, is that the "Value" column is further broken down by category

ID	event-time	sub-category	Value
1	2020-01-01	A	0.5
1	2020-01-01	B	0.2
1	2020-01-01	C	0.8
1	2020-01-02	A	0.9
1	2020-01-02	B	0.3
1	2020-01-02	C	0.6

Multiplication factor

sub-category	multiplication factor
A	1.7
B	1.2
C	1.9

Appreciate any help on this

@CNENFRNL