Forum Discussion
Cumulative total between two dates in monthly allocation
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 UserMich_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
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.
- amitchandak
Mich_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?