Forum Discussion
Helper ICumlative Total based on current axis date minus 60 days
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]
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
Microsoft EmployeeHi 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
)
)
Helper IHi 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
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
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