Cumulative total for month

Question

Hi All,I have run into a problem I cannot resolve. I have set of dates, Target and Actual numbers in a table. I need to calculate the cumulative total for each day of the month. The logic appears to be simple enough, but it doesn't work. Here is the data:DATE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TARGET ACTUAL RT_mnth st_day_mnth&nbsp; last_day_mnth1/1/20232738381/1/20231/31/20231/2/20233920201/1/20231/31/20231/3/20232637371/1/20231/31/20231/4/20233624241/1/20231/31/20231/5/20231823231/1/20231/31/20231/6/20232319191/1/20231/31/20231/7/20231423231/1/20231/31/20231/8/20233610101/1/20231/31/20231/9/20231440401/1/20231/31/20231/10/20231012121/1/20231/31/20231/11/20233625251/1/20231/31/20231/12/20232020201/1/20231/31/20231/13/20231037371/1/20231/31/20231/14/20233610101/1/20231/31/20231/15/20234029291/1/20231/31/2023'st_day_mnth' and 'last_day_mnth' are calculate measures from DATE. 'RT_mnth' is the cumulative total of ACTUAL column and has the following expression:RT_mnth = CALCULATE(&nbsp; &nbsp; SUM(TestSh[ACTUAL]), FILTER(TestSh, &nbsp; &nbsp; &nbsp; &nbsp; TestSh[DATE]&gt;= [st_day_mnth] &amp;&amp; TestSh[DATE] &lt;= [last_day_mnth]))I tried ALL and ALLEXCEPT functions but it does not give me the desired result. I have also tried SUMX with the table name and the column ACTUAL, but there is no change.&nbsp;Please suggest a suitable change to my code so that I get the cumulative total up to the date in the DATE column for each month. One solution is to change this to a date table and apply DATEMTD. But I want to understand why it is not working.&nbsp;Please suggest where I am going wrong.&nbsp;&nbsp;

fowmy · Accepted Answer

kkanda&nbsp;Use this measure:
RT_mnth =
VAR __StartDate =
    EOMONTH ( MAX ( TestSh[DATE] ), -1 ) + 1
VAR __LastDate =
    MAX ( TestSh[DATE] )
RETURN
    CALCULATE (
        SUM ( TestSh[ACTUAL] ),
        TestSh[DATE] &gt;= __StartDate,
        TestSh[DATE] &lt;= __LastDate
    )

Forum Discussion

Cumulative total for month

2 Replies

Related content

Cumulative Total

Cumulative Total

Cumulative total by month beginning in April

12-month rolling cumulative

SumX? - cumulative running total

1/1/2023	27	38	38	1/1/2023	1/31/2023
1/2/2023	39	20	20	1/1/2023	1/31/2023
1/3/2023	26	37	37	1/1/2023	1/31/2023
1/4/2023	36	24	24	1/1/2023	1/31/2023
1/5/2023	18	23	23	1/1/2023	1/31/2023
1/6/2023	23	19	19	1/1/2023	1/31/2023
1/7/2023	14	23	23	1/1/2023	1/31/2023
1/8/2023	36	10	10	1/1/2023	1/31/2023
1/9/2023	14	40	40	1/1/2023	1/31/2023
1/10/2023	10	12	12	1/1/2023	1/31/2023
1/11/2023	36	25	25	1/1/2023	1/31/2023
1/12/2023	20	20	20	1/1/2023	1/31/2023
1/13/2023	10	37	37	1/1/2023	1/31/2023
1/14/2023	36	10	10	1/1/2023	1/31/2023
1/15/2023	40	29	29	1/1/2023	1/31/2023