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

     

Mich_J's avatar
Mich_J
Frequent Visitor
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])
				))

 

mussaenda's avatar
mussaenda
Community Champion
3 years ago

Hi Mich_J ,

 

thanks for sharing this. 

It solved my requirement.