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]) ))
Community SupportHi, 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.
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]) ))
