Cumulative function based on a date range filter

Question

Hi everyone,&nbsp;I'm pretty new to using DAX function in Power BI and I'm having a hard time trying to create a cumulative function based on certain requirements. Please, someone help or maybe give some ideas&nbsp;on how I could modify the formula so that it does what I want. Any help would be appreciated.&nbsp;I have one date range filter in the report page&nbsp;&nbsp;and based on this I want to calculate the cumulative function in the following way:- for the first month in the range to have the result obtained from 3 measures, like this: measure1-measure2+measure3&nbsp;- and for the next months, until the last date in the date range filter, to take the result from the previous month + measure1- measure2.&nbsp;The date filter is taken form a DateTable which is linked with the tables where the measures are.The formula that I have right now is this :&nbsp;CumulativeResult =VAR StartDate = MIN('datatable'[Date])VAR EndDate = MAX('datatable'[Date])VAR SelectedRange = FILTER('datatable', 'datatable'[Date] &gt;= StartDate &amp;&amp; 'datatable'[Date] &lt;= EndDate)RETURN&nbsp; &nbsp; SUMX(&nbsp; &nbsp; &nbsp; &nbsp; SelectedRange,&nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'datatable'[Date] = StartDate,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [measure1] - [measure2] + [measure3],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [measure1] - [measure2],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL('datatable'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'datatable'[Date] = EARLIER('datatable'[Date])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ))&nbsp;Thanks!

lbendlin · Answer

Most of the manual work you did can be gotten for free through the filter context.&nbsp; Using ALLSELECTED(), for example.
&nbsp;
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216 Please show the expected outcome based on the sample data you provided. https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Forum Discussion

Cumulative function based on a date range filter

1 Reply

Related content

Cumulative Total

Caclulate function over riding filter?

filter function

Creating Cumulative Graphs

Cumulative Total