Getting sum of amount between two date range

[Anonymous]

Hi Team,

I have following issue:

I've amount table and detail table. I want a measure to get sum of all the amount in a card.

I used following dax:

Total = VAR startdate = MIN('details'[dates])

VAR enddate = SELECTEDVALUE(details[dates])

return

calculate ( SUM(amount[Amount] ),

FILTER (amount,amount[dates]>=startdate && amount[dates] <=enddate))

But it gives me amount for the selected month from the slicer. I want sum of amount from start date to the selected date.

Detail:

date1	Employee	age
01/01/19	a	31
02/01/19	b	32
03/01/19	c	45
04/01/19	d	24
05/01/19	e	32
11/21/19	f	21

 

Amount:

dates	Amount
01/01/19	100
02/01/19	121
03/01/19	212
04/01/19	32
04/01/19	134

For slicer I'm using date1 column.

So when I'm selecting March, I'm getting output 212. I want 433.

Please help!

Thanks in advance!!

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

Assuming that you have created the relationship between the two tables.

Please try the measure below.

Measure =
CALCULATE (
    SUM ( 'Table 2'[Amount] ),
    FILTER (
        ALLSELECTED ( 'Table'[date1] ),
        'Table'[date1] <= MAX ( 'Table'[date1] )
    )
)

Here is the output.

More details, you could refer to my attachment.

Best Regards,

Cherry

[jthomson]

Could you not just modify your slicer?

[Anonymous]

@jthomsonNo. I've to use the given slicer only

[amitchandak]

Have you tried

VAR enddate = max(details[dates])

I done something like this 

https://medium.com/chandakamit/power-bi-comparing-data-across-date-ranges-36be49b68613