Forum Discussion

Mich_J's avatar
Mich_J
Frequent Visitor
6 years ago
Solved

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 ...
Need Help
  • Mich_J's avatar
    Mich_J
    6 years ago

    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])
				))

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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

Mich_J's avatar
Mich_J
Frequent Visitor
6 years ago

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.