cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SimonChung_GGGG
Helper III
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

image.png

 

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

image.png

 

Many many thanks!

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

Hi @SimonChung_GGGG ,

 

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.

1-2.PNG

Here is my test file for your reference.

 

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

View solution in original post

5 REPLIES 5
v-eachen-msft
Community Support
Community Support

Hi @SimonChung_GGGG ,

 

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.

1-2.PNG

Here is my test file for your reference.

 

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

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

amitchandak
Super User
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
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

 

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 !!
JustJan
Responsive Resident
Responsive Resident

Hi @SimonChung_GGGG 

 

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

@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

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

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

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

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

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!