cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Getting sum of amount between two date range

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resident Rockstar

Hi @valentina14 ,

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

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Highlighted
Super User I

Could you not just modify your slicer?

Highlighted
Helper I

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

Highlighted
Super User IV

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

Proud to be a Super User!

Highlighted
Resident Rockstar

Hi @valentina14 ,

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

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors