Monthly Distribution from year

[Rasmus7700]

Hello Guys,

I'm importing my budget scheme from excel, where i have one line like following:

Date	Account	Value	Posting Type
01-01-2024	13000	54.000.000	Budget

Then i've tried to make a measurer that can distribute this amount on each month multiplied with a "Distribution factor" for each month. So basically i want the measure to return (Yearly Amount / 12) * Monthly Distribution.

Then i can visualize this month by month beside the already realised numbers.... 

I've made a lot of variants of following code - but can't come to a solution - please help 🙂 

Test - Budget = 
    VAR SelectedYear = 
        SELECTEDVALUE('Dim.Kalender'[År #])
    
    VAR YearlyBudget = SUMX(
    FILTER('Finans - Kontoplan - Finansposter - Fact', 'Finans - Kontoplan - Finansposter - Fact'[Bogførings Dato] >= DATE(SelectedYear,"01","01") && 'Finans - Kontoplan - Finansposter - Fact'[Posteringstype (Budget / Realiseret)] == "Budget"),'Finans - Kontoplan - Finansposter - Fact'[Total Beløb (Debet / Kredit)])
            
    VAR MonthlyDistribution = CALCULATE(
        VALUES('Dim.Kalender'),
        SWITCH(
            'Dim.Kalender'[Måned #],
            1, 1,
            2, 1.1,
            3, 1.1,
            4, 1,
            5, 1.1,
            6, 1,
            7, 0.85,
            8, 0.85,
            9, 1,
            10, 1.1,
            11, 1.1,
            12, 0.8,
            0
    )
    )
   RETURN
    
    CALCULATE((YearlyBudget / 12) * MonthlyDistribution)

    

[v-tangjie-msft]

Hi @Rasmus7700 ,

Please try:

VAR SelectedYear =
    SELECTEDVALUE ( 'Dim.Kalender'[År #] )
VAR SelectedMonth =
    SELECTEDVALUE ( 'Dim.Kalender'[Måned #] )

VAR YearlyBudget =
    SUMX (
        FILTER (
            allselected('Finans - Kontoplan - Finansposter - Fact'),
            'Finans - Kontoplan - Finansposter - Fact'[Bogførings Dato]
                >=DATE(SelectedYear ,SelectedMonth ,1)

                && 'Finans - Kontoplan - Finansposter - Fact'[Posteringstype (Budget / Realiseret)] = "Budget"
        ),
        'Finans - Kontoplan - Finansposter - Fact'[Total Beløb (Debet / Kredit)]
    )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Daniel29195]

Hello @Rasmus7700 

i would suggest to add the distribution factor the datetable table as a column, instead of managing in the dax code . 

try this code and tell me if it works : 

Test - Budget =
VAR SelectedYear =
    SELECTEDVALUE ( 'Dim.Kalender'[År #] )

VAR selectedmonth =  SELECTEDVALUE('Dim.Kalender'[Måned #])

VAR YearlyBudget =
    SUMX (
        FILTER (
            'Finans - Kontoplan - Finansposter - Fact',
            'Finans - Kontoplan - Finansposter - Fact'[Bogførings Dato]
                >= DATE ( SelectedYear, "01", "01" )
                && 'Finans - Kontoplan - Finansposter - Fact'[Bogførings Dato] <=DATE ( SelectedYear, "12", "31" )
                && 'Finans - Kontoplan - Finansposter - Fact'[Posteringstype (Budget / Realiseret)] == "Budget"
        ),
        'Finans - Kontoplan - Finansposter - Fact'[Total Beløb (Debet / Kredit)]
    )
VAR MonthlyDistribution = 
filter(
'Dim.Kalender',
'Dim.Kalender'[År #] = SelectedYear  &&  'Dim.Kalender'[Måned #] = selectedmonth
 )
RETURN
    CALCULATE (
        ( YearlyBudget / 12 ) * MonthlyDistribution
    )

If my response has successfully addressed your issue, kindly consider marking it as the accepted solution ✅.

This not only helps you keep track of resolved matters but also assists other community members who may encounter similar challenges. Additionally, your acknowledgment by giving a thumbs up 👍 is greatly appreciated. Thank you for contributing to the community's collaborative spirit.

[Fowmy]

@Rasmus7700 

Please try this measure. If this doesn't work, can share a dummy PBIX file, it will be helpful to understand your data model.

Test - Budget =
VAR SelectedYear =
    SELECTEDVALUE ( 'Dim.Kalender'[År #] )
VAR SelectedMonth =
    SELECTEDVALUE ( 'Dim.Kalender'[Måned #] )
VAR YearlyBudget =
    SUMX (
        FILTER (
            'Finans - Kontoplan - Finansposter - Fact',
            'Finans - Kontoplan - Finansposter - Fact'[Bogførings Dato]
                >= DATE ( SelectedYear, 1, 1 )
                && 'Finans - Kontoplan - Finansposter - Fact'[Posteringstype (Budget / Realiseret)] = "Budget"
        ),
        'Finans - Kontoplan - Finansposter - Fact'[Total Beløb (Debet / Kredit)]
    )
VAR MonthlyDistribution =
    SWITCH (
        SelectedMonth,
        1, 1,
        2, 1.1,
        3, 1.1,
        4, 1,
        5, 1.1,
        6, 1,
        7, 0.85,
        8, 0.85,
        9, 1,
        10, 1.1,
        11, 1.1,
        12, 0.8,
        0
    )
RETURN
    CALCULATE ( DIVIDE ( YearlyBudget, 12 ) * MonthlyDistribution )

[Rasmus7700]

The solution is working like ot should for one month - here it get's the correct values like intended...

The problem is that the value where i am getting the "Budget" value from is only represented once in my raw data with a timestamp like "01-01-2024" - when using this formulas i get the correct value for January Month - but not fo the other months represented in my Matrix.... 

So i need to adapt the code in a way so the SUMX fucntion always will take the value from >= "01-01-2024" with the state "Budget".... As i see it here it takes the values it finds within the monthly represented column

Then it need to set the MonthlyDistribution for each month represented in my Matrix....