cancel
Showing results for
Did you mean:
Helper III

## Sum up amount before selected slicer date

Hi PB Buddies,

I would like to have a measure, it would sum up the amount before the selected start date with date slicer changing,
For example, when the date slicer selected 5 Jan to 9 Jan, the result is 4+5+3+10 = 22

When the date slicer selected 3 Jan to 9 Jan, the result is 4+5 = 9

Many many thanks!

1 ACCEPTED SOLUTION
Community Support

At first, you need to create a new date dimention as a slicer.

Then refer to the following measure.

``````Measure =
VAR a =
CALCULATE ( MIN ( 'Table 2'[Date] ), ALLSELECTED ( 'Table 2'[Date] ) )
RETURN
CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( 'Table', 'Table'[Date] < a ) )``````

Here is the result.

Here is my test file for your reference.

If this post helps, then please consider Accept it as the solution to help the other members find it.
5 REPLIES 5
Community Support

At first, you need to create a new date dimention as a slicer.

Then refer to the following measure.

``````Measure =
VAR a =
CALCULATE ( MIN ( 'Table 2'[Date] ), ALLSELECTED ( 'Table 2'[Date] ) )
RETURN
CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( 'Table', 'Table'[Date] < a ) )``````

Here is the result.

Here is my test file for your reference.

If this post helps, then please consider Accept it as the solution to help the other members find it.
Helper III

@v-eachen-msft
It's work, many thanks!

Super User

First, make sure you have date dimension

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer

After you can try

``````Value =
var _min = mixx(date,date[date])
Return
calculate(sum(sales[sales Amount]),sales[sales_date]<_min))``````

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
Responsive Resident

I think the measure ican be something like :

`TotalBeforeDate := var startdate = min('Dates'[Date])var result = sumx('Table', if('Table'[Date]<startdate, 'Table'[Amount]))return result`

The startdate refers to the date column you use in the slicer.

Hope this helps

Helper III

@JustJan
Thanks for your help! however it doesn't work!

The slicer will take control only on the selected date bounded, I think it should add function like ALL( ), but I can't do that

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!