Cumulative SUM of a column for a specific date range

[kala2]

Assuming we have a specific date range in our table.

With this DAX i calculate the SUM of a specific column for a specific date start and date end

MeasureSumColumn1ForADateRange =
   CALCULATE(SUM(Table[Column1]),
    FILTER (
        Table,
        Table[Chart Start Date] <= ENDOFMONTH ( Table[Date] ) &&
        Table[Chart End Date] >= STARTOFMONTH ( Table[Date] )
    )
)

I want to achieve cumulative SUM for the specific date range. i.e

                           Column1         Column1Cumulative

February 17        20.400 USD     20.400 USD

March 17            62.400 USD     82.800 USD

April 17              40.800 USD      121.600 USD

Any ideas how to achieve this?

[v-chenwuz-msft]

Hi @kala2 ,

I made some data based on your description. Your intent should be to have the chart graph be able to follow the specified range date for display.

Let's say the chart starts on 2/15/2017 to 3/14/2018, then the chart would only have data for those months. These range dates are selectable by the user and are dynamically displayed in the chart as expected. And the Cumulative values should be accumulated from the specified start date?

If i am right, please try some measures like the following:

MeasureSumColumn1ForADateRange =
VAR _RangeStart =
    MIN( 'CALENDAR TABLE'[Date] )
VAR _RangeEnd =
    EOMONTH( MAX( 'CALENDAR TABLE'[Date] ), 0 )
VAR _CurrnetEndOfMonth =
    EOMONTH( MAX( 'Table'[Date] ), 0 )
VAR _SUM =
    CALCULATE(
        SUM( 'Table'[column1] ),
        ALLEXCEPT( 'Table', 'Table'[Project Name] ),
        'Table'[Date] >= _RangeStart,
        'Table'[Date] <= _CurrnetEndOfMonth
    )
RETURN
    IF( _RangeEnd < _CurrnetEndOfMonth, BLANK(), _SUM )

Result: 

(no filter, all data)

(filtered by specified range date  and project)

Pbix file in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[kala2]

Hi @v-chenwuz-msft ,

Thanks for your detailed reply, i will give it a look today.

Well the main goal is that a user will not have a date selection. All the filter by start and end date will happen dynamically, based on the table start/end date values.

So each row will have a start/end date. If we select a specific let's say project then ta cummulative results will be calculated within only the time period.

[v-chenwuz-msft]

Hi @kala2 ,

If you have any questions, please let me know. If you can, sharing some example data  or pbix file without sensitive info is the best choice.

[kala2]

Hi @v-chenwuz-msft ,

I've created a minimum reproduction .pbix file.
When we select a project name, the costs cumulative total should be happen only between the start date and end date

Uploaded .pbix file
https://www.dropbox.com/sh/1fyp0hks3p0q4hm/AABNEJBXJconaSA1qv0jtPbHa?dl=0

[v-chenwuz-msft · Accepted solution]

Hi @kala2 ,

DAX 

test measure form css =
VAR _s =
    SELECTEDVALUE( Projects[Chart Start Date] )
VAR _e =
    SELECTEDVALUE( Projects[Chart End Date] )
VAR _p =
    SELECTEDVALUE( Projects[Projects] )
VAR _sumselect =
    CALCULATE(
        SUM( 'Costs'[Costs] ),
        FILTER(
            ALL( Costs ),
            [Projects] = _p
                && [Date] >= _s
                && [Date] <= _e
                && [Date] <= MAX( 'Costs'[Date] )
        )
    )
VAR _sumnoselect =
    CALCULATE(
        SUM( Costs[Costs] ),
        FILTER( ALL( Costs ), [Date] <= MAX( 'Costs'[Date] ) )
    )
RETURN
    IF(
        _s = BLANK(),
        _sumnoselect,
        IF( _e < MAX( Costs[Date] ), BLANK(), _sumselect )
    )

Result:

I upload the pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[kala2]

Hi @v-chenwuz-msft ,

Thank you! Works like a charm! 🙂
You are a hero! 😛

[kala2]

Hi @Vera_33, Thanks for the response.

Tried your solution, the problem is that cumulative is happening correctly but now Table is not filtered by start date and end date. ALL is the problem, because if i remove it then table is filtered with start date and end date but then no cumulative sum is happening

What i want is to filter the data by start date and end date and then make the cumulative sum.

MonthlyMTAMTCosts = 
VAR maxDate =
    ENDOFMONTH ( 'Table'[Date] )
VAR T1 =
    FILTER (
        ALL('Table'),
        'Table'[Project Name] = SELECTEDVALUE('Table'[Project Name]) <-- ADDED FILTER BY SELECTED PROJECT
        && 'Table'[Chart Start Date] <= ENDOFMONTH('Table'[Date]) && 'Table'[Chart End Date] >= STARTOFMONTH('Table'[Date])
    )
RETURN
    SUMX ( FILTER(T1, [Date] <= maxDate),  [Column1])

Chart supposed to go until May of 2020

[Vera_33]

Hi @kala2 

That's the risk to write DAX without enough context, I was doing it with my assumption and dummy data...not sure why you have a Chart start date and end date in the same fact table...try to add a IF statement and see how it goes

[Ashish_Mathur]

Hi,

My approach would be to split each row into as many rows as there are months in the date range (using the Query Editor).  So i would split the row in your first image into 3 rows.  This will simplify DAX formula writing and any further analysis that you want to do.  Will the start and end date in each row always be the first of every month?  If not, how should the amount be split on proportionate number of days in each month.  Also, if the end date is the first date of each month, shouldn't there be a row in your final visual for the first of that month? 

[Vera_33]

Hi @kala2 

try it

cum_sum =
VAR maxDate =
    ENDOFMONTH ( 'Table'[Date] )
VAR T1 =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Chart Start Date] <= ENDOFMONTH ( 'Table'[Date] )
            && 'Table'[Chart End Date] >= STARTOFMONTH ( 'Table'[Date] )
    )
RETURN
    SUMX ( FILTER ( T1, [Date] <= maxDate ), [Column1] )