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!

Hi Anonymous , 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.

Sum up amount before selected slicer date | Microsoft Fabric Community

5 Replies

v-eachen-msft
Community Support
6 years ago
Hi Anonymous ,

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.

Sum up amount before selected slicer date.pbix52 KB
- Anonymous
  Not applicable
  6 years ago
  v-eachen-msft
  It's work, many thanks!
Anonymous
Not applicable
6 years ago
Hi Anonymous

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
- Anonymous
  Not applicable
  6 years ago
  Anonymous
  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
amitchandak
Super User
6 years ago
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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Forum Discussion

Sum up amount before selected slicer date

5 Replies

Related content

Count Lastest Records Before Selected Date

Power Query Add rows of zero amount

Calculate bonus amount for retroactive months.

deduct amount from previous selected value in a table

Show days before the selected date