Cumulative Sum over Different periods / Units

[Anonymous]

I suspect this is easy… any help or guidance much appreciated.

I have a table (contractTable) with a unique Customer ID, Contract Start date, Contract End Date and, Units per Year… (and a dimDate table).

contractTable

I want to plot the Cumulative Sum from the earliest Start to latest End, recognizing that Customers have different start and end dates and different Units per year.

I created a Measure (CumulativeProfile) that returns the Sum of Units per Year (i.e. without year over year cumulative) … does not help.

CumulativeProfile =

CALCULATE

(

    SUM('contractTable'[Units per Year]),

    (

        FILTER (

        ALL('contractTable'),

        ''contractTable'[start].[Date] <= MAX(dimDate[Date].[Date]

            )

        )

    ))

[Anonymous]

In the immortal words of Wayne and Garth, "I'm not worthy".

Thanks, works like a charm.

[Anonymous]

Thanks @datamodel ! 

I'm close to the solution required but, suspect I am missing an obvious detail as it relates to plotting this Cumulative data. The Cumulative sum is available for the start and end dates but, not for the dates between. Something like:

I am using my dimDate table as the x-axis which, is related (1:*) to the Contract End Date.  What am I missing?

Thanks in advance.

[datamodel · Accepted solution]

How about cross joining to a summarized dimDate table to apply the value to each end date for each year?
You'll end up with a contractTable, dimDate, and a virtual contractTableExtended that is a cross join between the two. The relationship will be between dimDate[Date] and contractTableExpanded[maxDimDate]

contractTableExpanded =
ADDCOLUMNS (
FILTER (
CROSSJOIN ( contractTable, SUMMARIZE(dimDate,dimDate[Year],"lastDate",LASTDATE(dimDate[Date]) )),
[lastDate] >= contractTable[Start]
&& [lastDate] <= contractTable[End]
),
"maxDimDate", [lastDate]
)

[datamodel]

How's this?

1. Calculated Table that cross joins the contract table with a generated series of years from 2020 to 2030

contractTableExpanded =
var Years = SELECTCOLUMNS(GENERATESERIES(2020,2030,1),"Year",[Value])
RETURN
ADDCOLUMNS (
FILTER (
CROSSJOIN ( contractTable, Years ),
[Year] >= contractTable[Start].[Year]
&& [Year] <= contractTable[End].[Year]
),
"YearName", [Year]
)

2. Measure to sum the Units

Units = CALCULATE(SUM('contractTableExpanded'[Units per Year]))

3. Measure to sum cumulative units

Cumulative Units = IF (
    ISBLANK ( [Units] ),
    BLANK (),
    CALCULATE (
        [Units],
      FILTER (
        ALL ('contractTableExpanded'[Year] ),
       'contractTableExpanded'[Year] <= MAX('contractTableExpanded'[Year])
    )    
  )
)

[V-lianl-msft]

Hi @Anonymous ,

Unfortunately, based on the information you provided, I tried to create a table, and the two tables could not establish the right relationship.

As @Greg_Deckler  said before,You may think about recreating the table.

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

[Greg_Deckler]

Seems to me that you are going to want to create a new table using GENERATESERIES between your start and stop years and then mash that up with your units.

[Anonymous]

Thanks for your reply @Greg_Deckler (and @V-lianl-msft), 

Sorry, I'm not following your suggestion. 

I created (via generateSeries) a Table of dates ranging from earliest Start and, latest End.  But, I'm not clear on how this Table is used differently from my dimension table (dimDate) that is related (1:*) to my End date field.

How do I mashup Units with the Table created via generateSeries...?