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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
miloviajando
New Member

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

Hi @miloviajando ,

 

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
miloviajando
New Member

@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
Super User
Super User

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!




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!




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!




Hi @miloviajando ,

 

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

@miloviajando , 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))

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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