dates between

[Anonymous]

what syntax should i use to calculate the total change in Total Fund AUM between any date period (might be over a quarter, several months, or years) and how would I then calculate the % change over similar time periods. 

[Interkoubess · Accepted solution]

Hi @Anonymous,

Could you please give a sample data or Pbix document and explained the expected output?

Thank you.

[Anonymous · Accepted solution]

Date	Sum of Total Fund AUM (€)
29/05/15	56,925,158.70
30/06/15	60,891,932.05
31/07/15	60,337,044.27
31/08/15	59,042,780.04
30/09/15	56,309,602.23
30/10/15	58,346,595.71
30/11/15	57,140,401.57
30/12/15	55,540,912.99
29/01/16	53,740,042.79
29/02/16	51,836,507.49
31/03/16	54,670,963.96
29/04/16	56,804,403.24
31/05/16	56,873,734.39
30/06/16	56,923,009.12
29/07/16	59,465,623.74
31/08/16	61,406,589.78
30/09/16	61,190,605.66
31/10/16	62,240,636.47
30/11/16	61,797,959.57
30/12/16	65,093,163.68
31/01/17	66,654,099.06
28/02/17	68,859,996.54
31/03/17	69,151,416.24
28/04/17	70,579,049.73
31/05/17	71,064,725.33
30/06/17	70,741,885.90
31/07/17	71,571,186.42
31/08/17	71,815,844.46

[Interkoubess · Accepted solution]

Hi @Anonymous,

Could you please give a sample data or Pbix document and explained the expected output?

Thank you.

[Anonymous]

---

@Interkoubess wrote:

Hi @Anonymous,

Could you please give a sample data or Pbix document and explained the expected output?

Thank you.

---

As requested. I have two columns one is a column with month end dates the other shows the value of assets at the end of each month. I would like to be able to measure the rise or fall in asset values over a start/end date which can be change dynamically so that I can analyse  how asset values change over time. I would also like to be calculate what the rise/fall in value would be as a % of the starting value. 
  

[Interkoubess]

Hi @Anonymous,

Thank you for the data but it is a picture and I cannot copy and paste, please copy paste and then I can try some formulas.

Anyway in this case I will create a calendar table and link it to your data table and then create these measures below:

Monthly Fund= calculate(sum([Total Fund AUM])

Previous Monthly Fund=calculate([Monthly Fund], previousmonth(calendar[Date])

Diff= [Monthly Fund]-[Previous Monthly Fund]

Pourcentage= divide([Diff],[Previous Monthly Fund],blank())

And then I will put it in a report ( for example table).

Thank you.

Ninter

[Anonymous · Accepted solution]

Date	Sum of Total Fund AUM (€)
29/05/15	56,925,158.70
30/06/15	60,891,932.05
31/07/15	60,337,044.27
31/08/15	59,042,780.04
30/09/15	56,309,602.23
30/10/15	58,346,595.71
30/11/15	57,140,401.57
30/12/15	55,540,912.99
29/01/16	53,740,042.79
29/02/16	51,836,507.49
31/03/16	54,670,963.96
29/04/16	56,804,403.24
31/05/16	56,873,734.39
30/06/16	56,923,009.12
29/07/16	59,465,623.74
31/08/16	61,406,589.78
30/09/16	61,190,605.66
31/10/16	62,240,636.47
30/11/16	61,797,959.57
30/12/16	65,093,163.68
31/01/17	66,654,099.06
28/02/17	68,859,996.54
31/03/17	69,151,416.24
28/04/17	70,579,049.73
31/05/17	71,064,725.33
30/06/17	70,741,885.90
31/07/17	71,571,186.42
31/08/17	71,815,844.46

[Interkoubess · Accepted solution]

Hi @Anonymous,

I created a calendar table and linked it to the data table ( called data) and the I created these measures below ( you can create only 2 measures with variables):

Monthly Fund = CALCULATE(SUM(Data[Fund AUM (€)]))

Prev Monthly Fund = CALCULATE([Monthly Fund],PREVIOUSMONTH(TabCalen[Date]))

Diff = [Monthly Fund]-[Prev Monthly Fund]

Percentage = DIVIDE([Diff],[Prev Monthly Fund],BLANK())

here are the relashionships:

And then the report....

Let us know if you have any observations...

Ninter

[Anonymous]

Thanks Ninter. Can you explain why you use a calendar table when there is a date column already in the table. Should one always use a calendar table in every model?