How to calculate running totals starting from a specific date to the latest date of my data table?

[Rck7]

Hi Friends,

I have data values in my data table from dates starting from OCT 14- Latest date.  I am looking to create a measure to calculate Running totals for the values starting from Oct 15 - Latest date (excluding the year 2014) with out using any date slicers. How would I be able to do that?

Date column structure:

Kindly, help!!!!
Thank you.

[Anonymous · Accepted solution]

You can try this one:

I'm assuming that the column [UsageDate] is in your FACT table, and relates to the [Date] column in your CALENDAR table.  Please note that time intelligence like this will require a calendar table.  There is lots of good documentation on building a good calendar table. Try looking here, Matt Alington has made a nice table and posted it on PowerPivotPro.

[Running Total Measure] :=
CALCULATE (
    [Base Measure],
    //likely summing up a certain column
    FILTER (
        ALL ( CalendarTable ),
        CalendarTable[Date] >= DATE ( 2015, 1, 1 ) //Currently hardcoded to 1/1/2015, but this could be made dynamic later
            && CalendarTable[Date] <= MAX ( CalendarTable[Date] )
    )
)

Also look at PowerPivotPro's articles on the Greatest Formula In The World...GFITW.  This measure is based on that pattern.

Cheers,

~ Chris H