Measure to get cumulative total for months in the future from current month.

[balaganeshmohan]

I have been trying to write a measure that can calculate the sum of a qty column based on date values in the past and in the future per month. The months should start from current month until the end which is always august.

planned date	qty
1/1/2022	12
1/2/2022	14
1/3/2022	123
1/4/2022	66
1/5/2022	55
1/6/2022	55
1/7/2022	98
1/8/2022	23
1/9/2022	78
2/1/2022	45
2/2/2022	19
1/1/2023	76
2/1/2023	18
12/11/2023	73

The results I expect are,

DEC	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG
5	18+5	23+16	.	.	.	.	.	.

I've tried the SQLbi tutorial, but that doesn't seem to work, some assistance is greatly appreciated.

OutstandingAmountPerMonth = 
VAR LastVisibleDate =  
    MAX('Date'[Date])
VAR FirstVisibleDate = 
    MIN('Date'[Date])
VAR LastDateWithOutstanding = 
    CALCULATE(
        MAX(Sales_Line[Planned Shipment Date]),
        REMOVEFILTERS()
    )
VAR Result = 
    IF( FirstVisibleDate < LastDateWithOutstanding,
        CALCULATE( SUM( Sales_Line[Outstanding Amount]), 'Date'[Date] <= LastVisibleDate, Sales_Line[Type_(sales_order_assignment)] = BLANK() ) )
RETURN 
    Result

@amitchandak 

[v-xiaotang]

Hi  @balaganeshmohan 

Thanks for reaching out to us.

It seems there is no relationship between sample data and expected output, so it is difficult to understand how your expected result is calculated. Theoretically, the cumulative total is the sum of the current month and the previous total. Please reshare your expected output, thanks

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.