Sum up amount before selected slicer date

[Anonymous]

 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!

[v-eachen-msft · Accepted solution]

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.

[Anonymous]

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

[amitchandak]

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

[Anonymous]

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]

@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