Cumulative SUM for a date range ignore date range

[kala2]

I want to make Cumulative SUM of the values of a column for a specific date range and by specific selected Project

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 table by start date and end date and then make the cumulative sum in the filtered table.

Measure Column Cumulative SUM= 
VAR maxDate =
    ENDOFMONTH ( 'Table'[Date] )
VAR T1 =
    FILTER (
        ALL('Table'),
        'Table'[Project Name] = SELECTEDVALUE('Table'[Project Name]) <-- FILTER BY SELECTED PROJECT FROM A SLICER
        && '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

[v-henryk-mstf]

Hi @kala2 ,

Need to create a separate date table as a judgment condition in the filter. Refer to a formula similar to the following:

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

If the problem is still not resolved, please provide detailed error information and some test data. Looking forward to your reply.

Best Regards,
Henry

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

[kala2]

ΗΙ @v-henryk-mstf ,

Thanks for your reply,
I want to achieve something like this:

 
Please take a look on this pbix
https://www.dropbox.com/sh/1fyp0hks3p0q4hm/AABNEJBXJconaSA1qv0jtPbHa?dl=0

[rbriga]

Alright.

What would help is creating a claendar table, which is best practice anyway.

That table will hold all dates as a key, then all the derived columns-year, months, year-month, etc.

Apply the logic on the new table. It should look roughly like this:

VAR maxDate =
    MAX(Calendar[Date])
VAR T1 =
    FILTER (
        CROSSFILTER(Calendar[Date],Table[Chart Start Date],OFF), --Assuming you will dreate the relationship to the calendar table on the start date. We turn it off because we want a cumulative result.
         'Table'[Chart Start Date] <= ENDOFMONTH(Calendar[Date]) && 'Table'[Chart End Date] >= STARTOFMONTH(Calendar[Date])

Give it a try- you might need some adjustments to fit it into your mold.

I do something similar with "active" entries- checking whether an entry with start\end dates was within the contextual month\week day.

Keep in mind, in the visual, the axis must be Calendar[Year-Month] rether than Table[Year-Month].

[kala2]

Could you please provide me a minimum reproduction pbix? That would be helpfull

[kala2]

Hey, i'm trying to integrate your solution but my version of power bi desktop doesnt allow me to add crossfilter inside filter.

Any workaround?

[kala2]

Hi, no still the same..

[rbriga]

Will replacing ALL('Table') with ALLSELECTED('Table') help?