Cumlative Total based on current axis date minus 60 days

[cgardyne]

I need a cumlative total based on the current axis date less 60 days. i.e today (29th May) is from 31st March - 29th May.

I have a table Date Sixty Days which contains a day for every day from today back 60 days which is used as the Axis on my graph.

I have a measure that can get the total of loans on a particular day where the user signed up within the last 60 days here:

Property = CALCULATE (
    SUMX (Loans, Loans[Amount] + Loans[Fee] ) + 0,
    FILTER ( 
		ALL ( Loans ),
		Loans[User Sign Up Date] >= Min('Date Sixty Days'[Last Sixty Days]) && Loans[User Sign Up Date] <= Max('Date Sixty Days'[Last Sixty Days]) && Loans[LoanProductId] = 12
	)
)

So in the above measure how do I get a cumlative total from the current date on the graph back 60 days? Is it possible in a measure?

[Phil_Seamark · Accepted solution]

Hi @cgardyne

For the last part of your question,  Just create a table with 1 column and three rows as follows:

Date Range
--------------
30
60
90

But don't create a relationship to any other table.  

Create a measure on this table like 

xyz = MIN('DateRangeTable'[Date Range])

You can then create a slicer using this table and add the measure to your original formula

So instead of Today - 60, you can use Today - [xyz]

[Phil_Seamark]

Hi @cgardyne

Have you tried the following slight adjustment?

Property =
CALCULATE (
    SUMX ( Loans, Loans[Amount] + Loans[Fee] )
        + 0,
    FILTER (
        ALL ( Loans ),
        Loans[User Sign Up Date] >= MIN ( 'Date Sixty Days'[Last Sixty Days] ) - 60
            && Loans[User Sign Up Date] <= MAX ( 'Date Sixty Days'[Last Sixty Days] ) - 60
            && Loans[LoanProductId] = 12
    )
)

[cgardyne]

Hi @Phil_Seamark,

Thanks for that, I haven't but I now see what you can do with that and it makes sense.

So with that I have a couple of general questions:

Does the date for 'Date Sixty Days'[Last Sixty Days] change to the current Axis value when I'm using the measure as a value of the same graph or does it always stay as the minimum value in the table? Because that needs to take the current date that's in the Axis, minus 60 days and total all loans that were created between that date and the current date in the axis. 

So if the first date on the axis is today - 60 days = 31st Mar, I need to go back 60 days from that (30th Jan) and sum all the loans that were created between 30th Jan and 31st Mar and plot that on the 31st Mar on the graph.

Does that make sense?

Is there a way (function, query etc) to make the 60 days a parameter? As I need to create this measure per product (we have 13) and for multiple time periods - 30, 60, 90. So if I could pass in the product and days that'd be amazing; happy to ask a new question if needed here.

Appreciate your time.

[Phil_Seamark · Accepted solution]

Hi @cgardyne

For the last part of your question,  Just create a table with 1 column and three rows as follows:

Date Range
--------------
30
60
90

But don't create a relationship to any other table.  

Create a measure on this table like 

xyz = MIN('DateRangeTable'[Date Range])

You can then create a slicer using this table and add the measure to your original formula

So instead of Today - 60, you can use Today - [xyz]

[cgardyne]

Hi @Phil_Seamark,

That works really well thanks.

Any idea how I could fix my first problem, hopefully I've made it clear enough.

[Phil_Seamark · Accepted solution]

HI @cgardyne

I think I understand your need.  You need to sum up loans over a 60 day period (or dynamice period)

you could try either this

Property =
CALCULATE (
    SUMX ( Loans, Loans[Amount] + Loans[Fee] )
        + 0,
    FILTER (
        ALL ( Loans ),
        Loans[User Sign Up Date] >= MIN ( 'Date Sixty Days'[Last Sixty Days] ) - 60
            && Loans[User Sign Up Date] <= MAX ( 'Date Sixty Days'[Last Sixty Days] ) 
            && Loans[LoanProductId] = 12
    )
)

or we could try using the DATESBETWEEN function.  Try this and let me know if the numbers look right