Cumulative total between two periods from different table

[Anonymous]

Hello,

I need to calculate the cumulative total for the selected period. Here, start date and end date is from two different tables. I have a date slicer between dates.  selecting the dates from 1/1/2020 to 31/05/2020. 

sample data:

2020           Amount           cumulative total
jan               25400              25400
feb               32600              58000
March          7900                65900
April            31900               97800
May             7100                104900

Now, when i select the period from 1/3/2020 to 31/05/2020

 I get the data for all the years

2020       cumulative total
March   7900
April     31900
May     7100

My result should be

2020       cumulative total
March   65900
April     97800
May     104900

the query i use, (this query displays the cumulative total for all the years). I need to display only for the selected period.

CB asset =
IF (
MIN ( 'Document Date'[Date] )
<= CALCULATE ( MAX ('Document Date'[Date] ), ALL ('Document Date'[Date] ) ),
CALCULATE([CB WIP Asset],
Filter(
ALLSELECTED('Document Date'),'Document Date'[Date]<=MAX('Document Date'[Date])))
)
Any idea is appreciated, Thanks.

[amitchandak]

@Anonymous , Try like

with date table

Cumm Sales = CALCULATE([CB WIP Asset],filter(allselected(date),date[date] <=max(date[Date])))

Cumm Sales = CALCULATE([CB WIP Asset],filter(allselected('Document Date'),'Document Date'[Date] <=max('Document Date'[Date])))

[Anonymous]

Thanks for your reply. This query will return the cumulative total till the selected period, doesnt return between the selected period. In my sampel data, 1/1/2020 from startdate table and 31/12/2020 from enddate table. I need to show the cumulative total  from start date to end date. Value for 1/1/2020 should have the cumulative total till 31 dec 2019. 

Document date is my dimension date table. 

startdate and enddate table query is

End Date =

ADDCOLUMNS (

CALENDAR (DATE(2012,1,1), TODAY())

)

we take the document date key by looking up on the dates from startdate and enddate table and do the calculation

If i use the below query, it returns the amount, for that particular period selected, but the value is not a cumulative total

Test CB Asset = CALCULATE([CB WIP Asset],

FILTER('Document Date','Document Date'[Document_Date_Key] > [StartDate_Key] && 'Document Date'[Document_Date_Key] <=[EndDate_Key]))

If i use the below query, it returns the cumulative total for the entire period, but i need to display the cumulative total for selected dates in slicer.

CALCULATE([CB WIP Asset],filter(allselected('Document Date'),'Document Date'[Date] <=max('Document Date'[Date])))

Sample data, 

sample       amount     cumulative total

jan 2020       20             30
feb 2020     10              40
march2020 60             100
April 2020    80            180
May 2020     20            200

if i select March 2020 to May 2020, My result should be
march    2020 100
April      2020 180
May       2020 200

Thanks.