topic Re: Calculating monthly values from yearly values in DAX Commands and Tips

Calculating monthly values from yearly values

DHR — Wed, 21 Feb 2024 10:15:46 GMT

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

Date	Measure
2021-05-31	-213,188
2021-06-30	213,188
2021-07-31	-42,638
2021-08-31	0
2021-09-30	42,638
2021-12-31	1,492,753
Total	1,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!

Re: Calculating monthly values from yearly values

lbendlin — Wed, 21 Feb 2024 18:37:05 GMT

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 .

Re: Calculating monthly values from yearly values

DHR — Wed, 21 Feb 2024 19:52:22 GMT

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?

Re: Calculating monthly values from yearly values

lbendlin — Wed, 21 Feb 2024 23:50:35 GMT

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.

Re: Calculating monthly values from yearly values

DHR — Thu, 22 Feb 2024 08:16:53 GMT

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:

Year	Month	AmountYTD	Monthly value
2020	6	-40 000	-40 000
2020	7	-40 000	0
2020	8	-40 000	0
2020	9	-40 000	0
2020	10	-40 000	0
2020	11	-40 000	0
2020	12	-14 957 500	-14 917 500
2021	1		0
2021	2		0
2021	3		0
2021	4		0
2021	5	-213 188	-213 188
2021	6		213 188
2021	7	-42 638	-42 638
2021	8	-42 638	0
2021	9		42 638
2021	10		0
2021	11		0
2021	12	1 492 753	1 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.

Year	Month	AmountYTD	Monthly value
2021	6		213 188
2021	7	-42 638	-42 638
2021	8	-42 638	0
2021	9		42 638
2021	10		0
2021	11		0
2021	12	1 492 753	1 492 753
Total			1 705 941

Re: Calculating monthly values from yearly values

lbendlin — Sat, 24 Feb 2024 19:35:56 GMT

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.