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

[Anonymous]

Hi, I have this table

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

[v-cgao-msft · Accepted solution]

Hi @Anonymous ，

Please try changing the slicer type to After.

selected_date = MIN('Calendar'[Date])

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

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

[Anonymous]

@amitchandak thanks.

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

But shows blank when I put the ">"

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

[bolfri]

Solution 1:

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

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]))
    )

[Anonymous]

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?

[bolfri]

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

[Anonymous]

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

[bolfri · Accepted solution]

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

[v-cgao-msft · Accepted solution]

Hi @Anonymous ，

Please try changing the slicer type to After.

selected_date = MIN('Calendar'[Date])

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

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]

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