Split Value between 12 next months

[Anonymous]

Hi,

I have this 'General Ledger Entry'-table which contains payment for a hotline agreements. The Hotline Agreement is valid from the payment date and the next year/12 months. And I therefore wants to split the values equally between these 12 months, where the Hotline Agreement is active, so I can compare this value to the time that is being used in support for this particular customer. However, these payments is yearly, and I therefore need to split these values equally between the next 12 months.

I don't have all dates in this table, and I therefore have to use a datetable to do this. I have used following DAX-formular to split this value up.

Hotlinesplit 9 =

Var firstmonth =

FIRSTDATE('Date'[Date])

Var Budgetmonth =

DATESINPERIOD('Date'[Date];firstmonth;-12;MONTH)

Var BudgettoDivide =

-CALCULATE(SUM('General Ledger Entry'[Amount]);'General Ledger Entry'[G_L Account No_] = 4040;'Date'[Date] IN Budgetmonth)

Var NumofMonths =

CALCULATE(DISTINCTCOUNT('Date'[Month Number of Year]);Budgetmonth)

Var monthlyamount =

Divide(BudgettoDivide;NumofMonths)

Return

Sumx(values('Date'[Month Number Of Year]);monthlyamount)

Its seems to work okay, and it seems like that the values is being equally divided over the next 12 months. But the issue is, when I in my view filters on a specific year, it won't sum the monthly values, which I just have divided it into.

As you see in the picture, I have tried to split 61.766, which is paid May 2019 and May 2020. And I have split them into the next 12 months 5.147,17, but the grand total is blank. Further, when I in the table below, filters after year it is either blank or it divide the value wrong. It should sum it up, based on the division into months

Any Ideas?

[Anonymous · Accepted solution]

Why not create a table with the monthly splits? It's very easy with Power Query. If you do this, your measures will be as simple as:

SUM( T[Column] )

and it'll be as fast as it can be.

Please do not make your life more complicated than it already is.

Your table should read something like:

Acc No. | Month | Payment

Then store your Acc No.'s with all their attributes (like the total sum) in a dimension and just create the right relationships between the above table, your Date table, and all the other relevant dimensions.

Best
D

[Anonymous]

Hi Darlove,

Thank for your respone. That seems a bit easier 🙂