Forum Discussion

DHR's avatar
DHR
Frequent Visitor
2 years ago

Calculating monthly values from yearly values

I have a table with YTD values and I am trying to calculate the monthly values form the YTD values. I am able to get the correct monthly value for all dates but my total is incorrect. I have searched...
DHR's avatar
DHR
Frequent Visitor
2 years ago

Thanks for the reply lbendlin 

 

The source only provides me with the YTD amount for each year and month. The date in the data table is a calculated column. Therefore I will only ever have dates for the end of the month and that is why I chose (possibly incorrectly) to have a date table only for the end of the month. I first tried with a complete date table but ran into trouble with the all values in the middle of the month but maybe there is a way around that that I'm missing?

 

Asuming I want the total for the whole year I could use AmountYTD as you suggested but that won't work if I want the total for monthly values of July to December as an example.

 

Any ideas as to how I can accomplish calculating the monthly amount and get a correct total? 

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Here is the calculation for the monthly amount

     

     

    You already have the correct total, it is your Dec 2021 YTD amount.  There is no need to compute that again.

     

     

    • DHR's avatar
      DHR
      Frequent Visitor
      2 years ago

      Thank you for taking the time to reply to me lbendlin 

       

      I apologize for not having explained my problem in more detail with examples from the beginning. As I wrote in my original question I do have the desired monthly values, it is only the totalt that I can't seem to get correct.

       

      A missing row in my source data indicates that the AmountYTD value is zero meaning that the monthly value for that month shoulde be the negative value from previous months AmountYTD (unless the month is January then it should be equal to AmountYTD in January as I am working with the calendar year).

       

      Here is my desired output:

      YearMonthAmountYTDMonthly value
      20206-40 000-40 000
      20207-40 0000
      20208-40 0000
      20209-40 0000
      202010-40 0000
      202011-40 0000
      202012-14 957 500-14 917 500
      20211 0
      20212 0
      20213 0
      20214 0
      20215-213 188-213 188
      20216 213 188
      20217-42 638-42 638
      20218-42 6380
      20219 42 638
      202110 0
      202111 0
      2021121 492 7531 492 753
      Total  -13 464 747

       

      Let say I want to filter on the period from June to December 2021 then I want the total to be the sum of the monthly values for the selected months.

      YearMonthAmountYTDMonthly value
      20216 213 188
      20217-42 638-42 638
      20218-42 6380
      20219 42 638
      202110 0
      202111 0
      2021121 492 7531 492 753
      Total  1 705 941

       

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        by filtering periods you are robbing yourself of the baseline. You wouldn't be able to do anything for the months before the first transaction in the filter context.

         

        I would suggest refactoring the data source so it provides actuals rather than  accumulations.