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 the forum and can see that an incorrect total is a common problem but I cannot figure out how to get the correct total. I have tried variants with SUMX and SUMMARIZE (which I have found in other answers) but as I am fairly new to DAX I probably don't understand these functions well enough to figure out how to do this correctly.

 

Here is the output I get

DateMeasure
2021-05-31-213,188
2021-06-30213,188
2021-07-31

-42,638

2021-08-310
2021-09-3042,638
2021-12-31

1,492,753

Total1,194,289

 

As you can see the total I get is 1,194,289 instead of the expected total of 1,492,753.

 

Here is a link to a test file for convenience

https://drive.google.com/file/d/19zNfapTc7tyF2nppHVV5bJEI3TOXvsbV

 

Any help would be greatly appreciated!

5 Replies

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

    Your calendar table is incomplete.  Needs to be contiguous and covering.

     

    You already have the AmountYTD  (for whatever reason), so might as well use it for the total, even if it makes little sense .

  • 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