Forum Discussion

954571's avatar
954571
Frequent Visitor
2 years ago
Solved

Annualised Value for varying months data

Hi,

I am looking for help to calculate the annualised amount of a cost from multiple months worth of source files where by using a date filter I can see the total cost in the months selcted and a corresponding annualised amount, i.e. if I choose to show data for January 2024 to May 2024 I want to me able to see the 4 months worth of data and then show and annualised amount based on those 4 month.
If i select April 2024 to May 2024 I then want the annualsised amount to correspond to those 2 months of data.

I have a data set that shows the cost in each month, where there are multiple rows of data for each month. Month data gets set to the start of each month (all cost in January 2024 shows as being January 1st in data set). Currentlyhave 15 months of data which will grow.
I have created a measure based on my "Reporting Month" column which will show how many months of data is showing based on what I filter for (using reporting month as a slicer) called "Mon Diff"
So if I set slicer to 01/01/2023 to 01/03/2024 I would have a value of 15 for "month diff" which changes as I update the date slicer.

"Mon Diff"

MonDiff =
VAR _RangeStart = MINX(ALLSELECTED('DataFiles'),'DataFiles'[Reporting Month])
VAR _RangeEnd = MAXX(ALLSELECTED('DataFiles'),'DataFiles'[Reporting Month])
RETURN
DATEDIFF(_RangeStart,_RangeEnd,MONTH)+1
 
I then created a column "Annualised Total" where I want to see the annualised amount of data based on the months I have selected in the date slicer.
 
Annualised Total = ('DataFiles'[Total Costs]/[MonDiff])*12
 
This seems to only work for when the date slicers are set to pick up all data. If I reduce to show only 10 months of data, I can see the total matches up to the amount expected (the 10 months of worth of costs) , I can see that "MonDiff" is show as 10 but the "Annualised Total" amount seems to pick up the right amount of total cost buy divides by 15 ( the max value for MonDiff calc as I have 15 months worth of data.
 
 

Am I going about this the wrong way or have I made some mistake?

Thanks
Need Help
  • 954571's avatar
    954571
    2 years ago

    Thanks Yang,
    That didn't work for me but I think that is probably because I didn't explain the source data properly. My total costs was a column I created in order to get all the values into the same currency. So when I tried to use you syntax the cost column didn't get picked up - I am assuming it is becasue it was a new column I created?

    I found a solution elsewhere on this site

     

    When my annualised total measure became

     

    Annualised Total = SUMX('Data Files',
    DIVIDE('Data Files'[Total Costs],[MonDiff])*12)
     
    Where "Total Costs" is a column created to put all costs in same currency.

    Thanks for help.

3 Replies

  • 954571's avatar
    954571
    Frequent Visitor
    2 years ago

    Thanks Yang,
    That didn't work for me but I think that is probably because I didn't explain the source data properly. My total costs was a column I created in order to get all the values into the same currency. So when I tried to use you syntax the cost column didn't get picked up - I am assuming it is becasue it was a new column I created?

    I found a solution elsewhere on this site

     

    When my annualised total measure became

     

    Annualised Total = SUMX('Data Files',
    DIVIDE('Data Files'[Total Costs],[MonDiff])*12)
     
    Where "Total Costs" is a column created to put all costs in same currency.

    Thanks for help.
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi 954571 ,

       

      I'm very happy that your problem has been solved. Could you please accept your own answer as the solution?

       

      Thank you and I wish you a happy life.

       

      Best Regards,
      Yang
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
      If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi 954571 ,

     

    It is sufficient to replace Annualized Total from the Calculated column with measure, as measure will consider the current filter context for aggregation calculations.

     

    Please try:

     

    This is the example data I created:

     

    This is the measure syntax I used:

    MonDiff = 
VAR _RangeStart = MINX(ALLSELECTED('DataFiles'),'DataFiles'[Reporting Month])
VAR _RangeEnd = MAXX(ALLSELECTED('DataFiles'),'DataFiles'[Reporting Month])
RETURN
DATEDIFF(_RangeStart,_RangeEnd,MONTH)+1

Total Costs = 1500

Annualised Total = 
('DataFiles'[Total Costs]/[MonDiff])*12

     

    The final page visualization is shown below:

     

    As you can see, it works fine.

     

    I would be grateful if you could provide me with the pbix file or sample data.

     

    Remember to remove sensitive data and do not log in to your account in Power BI Desktop when uploading the pbix file.

     

    If you have any other questions please feel free to contact me.

     

    The pbix file is attached.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!