Forum Discussion
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 ...
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
Super User
Super UserMich_J , refer if this can help
Or this file this sum up days between dates
https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0
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?