Forum Discussion
dates between
Hi Anonymous,
Could you please give a sample data or Pbix document and explained the expected output?
Thank you.
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 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
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.
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
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 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
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?