cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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:
date1Employeeage
01/01/19a31
02/01/19b32
03/01/19c45
04/01/19d24
05/01/19e32
11/21/19f21
 
Amount:
datesAmount
01/01/19100
02/01/19121
03/01/19212
04/01/1932
04/01/19134
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!!
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resident Rockstar
Resident Rockstar

Hi @valentina14 ,

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.

Capture.PNG

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.

View solution in original post

4 REPLIES 4
Highlighted
Super User I
Super User I

Could you not just modify your slicer?

Highlighted

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

Highlighted

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Resident Rockstar
Resident Rockstar

Hi @valentina14 ,

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.

Capture.PNG

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.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors
Users online (614)