cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Cumulative total between two dates in monthly allocation

Hello, I m trying to figure out formula to display monthly total between two dates for one record. Data sample:

 CUSTOMER START DATE END DATE MONTHLY GP 99555 27/01/2020 27/04/2020 10 99778 09/01/2020 27/08/2020 20 99816 01/04/2020 27/10/2020 15 7111 15/04/2020 27/10/2020 10 7147 10/04/2020 31/12/2020 15 715 01/04/2020 27/08/2020 30

Result should be

 CUSTOMER START DATE END DATE MONTHLY GP 2020-01 2020-02 2020-03 2020-04 2020-05 2020-06 2020-07 2020-08 2020-09 2020-10 2020-11 2020-12 99555 27/01/2020 27/04/2020 10 10 10 10 10 99778 09/01/2020 27/08/2020 20 20 20 20 20 20 20 20 20 99816 01/04/2020 27/10/2020 15 15 15 15 15 15 15 15 7111 15/04/2020 27/10/2020 10 10 10 10 10 10 10 10 7147 10/04/2020 31/12/2020 15 15 15 15 15 15 15 15 15 15 715 01/04/2020 27/08/2020 30 30 30 30 30 30

Ideally it would be

``````CumulativeBetweenDates:=TOTALYTD(SUM(Opportunity[BaseYear_MonthAGP]),
'Calendar'[Day],USERELATIONSHIP([START DATE],'Calendar'[Day]),[END DATE]])``````

Unfortunatelly last parameter is a string and can't be used in this manner.

This would be run on SSAS threfore some limitations are in place(Direct Query, VAR not available)

Pointing to right direction would be appriciated

Michal

1 ACCEPTED SOLUTION
Frequent Visitor

Hello @v-alq-msft  and @amitchandak ,

Thanks for your support on this. Unfortunately both of the solutions did not solve calculation in SSAS environment but taking under consideration both comments and advice from local guru I came to above which did allocate GP across months.

``````CumulativeBetweenDates:=CALCULATE(
SUM(Customer[DAY_AGP]),
FILTER(CALCULATETABLE(Customer,ALL('Calendar')),
Customer[START_DATE] <=MAX('Calendar'[Day])&&
Customer[END_DATE]>MIN('Calendar'[Day])
))``````

7 REPLIES 7
Community Support

Hi, @Mich_J

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

``Calendar = CALENDARAUTO()``

There is no relationship between two tables. You may create three calculated columns and a measure as below.

``````Calculated column:
Year-Month = VALUE(FORMAT('Calendar'[Date],"yyyymm"))
StartYearMonth = VALUE(FORMAT('Table'[START DATE],"yyyymm"))
EndYearMonth = VALUE(FORMAT('Table'[END DATE],"yyyymm"))

Measure:
Result =
var _cYearMonth = SELECTEDVALUE('Calendar'[Year-Month])
var _startYearMonth = SELECTEDVALUE('Table'[StartYearMonth])
var _endYearMonth = SELECTEDVALUE('Table'[EndYearMonth])
return
IF(
_cYearMonth>=_startYearMonth&&_cYearMonth<=_endYearMonth,
SUM('Table'[MONTHLY GP])
)``````

Result:

Best Regards

Allan

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

Frequent Visitor

Hello @v-alq-msft  and @amitchandak ,

Thanks for your support on this. Unfortunately both of the solutions did not solve calculation in SSAS environment but taking under consideration both comments and advice from local guru I came to above which did allocate GP across months.

``````CumulativeBetweenDates:=CALCULATE(
SUM(Customer[DAY_AGP]),
FILTER(CALCULATETABLE(Customer,ALL('Calendar')),
Customer[START_DATE] <=MAX('Calendar'[Day])&&
Customer[END_DATE]>MIN('Calendar'[Day])
))``````

Super User

Hi @Mich_J ,

thanks for sharing this.

It solved my requirement.

Super User

@Mich_J , I did not get what you want to give the last parameter. You need to tell where year is going to end. In case it end at 12/31 no need for that. It is optional.

like

TOTALYTD(SUM(Opportunity[BaseYear_MonthAGP]),
'Calendar'[Day],USERELATIONSHIP([START DATE],'Calendar'[Day]),"12/31")

TOTALYTD(SUM(Opportunity[BaseYear_MonthAGP]),
'Calendar'[Day],USERELATIONSHIP([START DATE],'Calendar'[Day]),"3/31") // Year ending March

Frequent Visitor

@amitchandak This has to be dynamic parameter and its different for each record to achieve total between two dates therefore above won’t work. It needs to be time intelligence calculation but I cant find any references on web. Most calculations would work to summarise for each month based on date in one column. Where in here I would like to present monthly cumulative between two dates.

Super User
Frequent Visitor

@amitchandak I will try to evaluate your solution futher but taking my requirement and your example you would like to show

 Employee Start End 2020-1 2020-2 2020-3 2020-4 2020-5 2020-6 2020-7 A 03/03/2020 03/05/2020 0 0 1 1 1 0 0 B 10/02/2020 04/04/2020 0 1 1 1 0 0 0

Will your calculations cover this view?