Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

DAX - Get the sum of a column for dates greater than the one selected in a filter

Hi, I have this table

miloviajando_0-1671807572148.png


I need to:

- create a page filter to select just one date

- create a measure to SUM(Value) for dates that are > to the date selected.

 

For example:

- If I filter 26/12/2022, the measure will show 12

- If I filter 25/12/2022, the measure will show 33 (26/12/2022 + 27/12/2022 values)

 

Thanks

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi @Anonymous ,

 

Please try changing the slicer type to After.

vcgaomsft_0-1672020408599.png

 

selected_date = MIN('Calendar'[Date])
sell_exposure = 
var _max = MIN('Calendar'[Date])
var _total = 
CALCULATE(
    SUM('FxTrade'[SellExposure]),
    'FxTrade'[SettlementDate]>_max
)
return
_total

 

vcgaomsft_1-1672020512724.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

View solution in original post

The issue here is that you were mixing solution 1 & 2.

 

Solution 1 is then you want a slicer between or after on the slicer, so you're getting multiple values from Calendar table and Solution 2 is when you want end user to select only 1 date.

 

Let's change naming and see all the behaviours.

 

Solution A. 

Leaving what you have and on the slicer you want to select dates between the measure will be simply:

Solution A = SUM(FxTrade[SellExposure])
 
Selection on slicer: 2020-06-01 and 2020-12-31
Excpected results: show me a sum of SellExposure between 2020-06-01 and 2020-12-31 (no nothing after and before)
 
Solution B.
Changing behaviour on the slicer to be an after. Visual > Slicer settings > Style: After
Solution B = SUM(FxTrade[SellExposure])
 
Selection on slicer: 2020-06-01 (on this settings you can only select one date)
Expected results: show me a sum of SellExposure on and after 2020-06-01 (so nothing before)
 
Solution C.
Leaving a 2 dates on slicer, but expectting to see all the values after minimum dates on selection.
Solution C =
var minimum_selectedDate = FIRSTDATE('Calendar'[Date])
return CALCULATE(SUM(FxTrade[SellExposure]),FILTER(ALL('Calendar'),'Calendar'[Date] >= minimum_selectedDate))
 
 
Selection on slicer: 2020-06-01 and 2020-12-31
Expected results: show me a sum of SellExposure on and after 2020-06-01 (so nothing before), ignore the second date
 
Your PBIX file with all the solutions included: https://we.tl/t-zg2VnhzmiU




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
Anonymous
Not applicable

@amitchandak thanks.

Any ideas on why it works when I use the "="

miloviajando_0-1671809092068.png

 

But shows blank when I put the ">"

miloviajando_1-1671809134810.png

 

It should be summing the values on 11/01/2023, right?

 

bolfri
Solution Sage
Solution Sage

Solution 1:

 

You can change the behavior of the slicer Visual > Slicer settings > Style: After

bolfri_0-1671808181050.png

bolfri_1-1671808195736.png

Then the measure is simply Sum of Value = SUM([Value])

 

Solution 2:

Sum of value - Solution 2 = 
CALCULATE(
        SUM('Sample'[Value]),
        FILTER(ALL('Sample'[Date]),
        'Sample'[Date] >= SELECTEDVALUE('Sample'[Date]))
    )

bolfri_2-1671808411497.png

 

 

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Thanks, Option 2 is working for operator "=" but no for operator ">"

It is showing me BLANK values but there are values greaters than the date I am filtering. Any ideas why?

Can you send your pbix file via wetransfer.com with that issue? 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

https://drive.google.com/drive/folders/1Btr171OY_v6nfQL2jjwveX3ySsfAaBvY?usp=sharing
you will see a KPI with BLANK(), that's the measure. 
Thanks!

The issue here is that you were mixing solution 1 & 2.

 

Solution 1 is then you want a slicer between or after on the slicer, so you're getting multiple values from Calendar table and Solution 2 is when you want end user to select only 1 date.

 

Let's change naming and see all the behaviours.

 

Solution A. 

Leaving what you have and on the slicer you want to select dates between the measure will be simply:

Solution A = SUM(FxTrade[SellExposure])
 
Selection on slicer: 2020-06-01 and 2020-12-31
Excpected results: show me a sum of SellExposure between 2020-06-01 and 2020-12-31 (no nothing after and before)
 
Solution B.
Changing behaviour on the slicer to be an after. Visual > Slicer settings > Style: After
Solution B = SUM(FxTrade[SellExposure])
 
Selection on slicer: 2020-06-01 (on this settings you can only select one date)
Expected results: show me a sum of SellExposure on and after 2020-06-01 (so nothing before)
 
Solution C.
Leaving a 2 dates on slicer, but expectting to see all the values after minimum dates on selection.
Solution C =
var minimum_selectedDate = FIRSTDATE('Calendar'[Date])
return CALCULATE(SUM(FxTrade[SellExposure]),FILTER(ALL('Calendar'),'Calendar'[Date] >= minimum_selectedDate))
 
 
Selection on slicer: 2020-06-01 and 2020-12-31
Expected results: show me a sum of SellExposure on and after 2020-06-01 (so nothing before), ignore the second date
 
Your PBIX file with all the solutions included: https://we.tl/t-zg2VnhzmiU




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hi @Anonymous ,

 

Please try changing the slicer type to After.

vcgaomsft_0-1672020408599.png

 

selected_date = MIN('Calendar'[Date])
sell_exposure = 
var _max = MIN('Calendar'[Date])
var _total = 
CALCULATE(
    SUM('FxTrade'[SellExposure]),
    'FxTrade'[SettlementDate]>_max
)
return
_total

 

vcgaomsft_1-1672020512724.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

amitchandak
Super User
Super User

@Anonymous , In date slicer you have an option Greater (In small arrow, before dec 2022 and in Option -> Style , under visual property in Dec 2022)

 

Use that.

 

If not, the slicer needs to be on an independent table

 


//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Date] >=_max))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.