Forum Discussion

Rasmus7700's avatar
Rasmus7700
Frequent Visitor
2 years ago

Monthly Distribution from year

Hello Guys,

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

DateAccountValuePosting Type
01-01-20241300054.000.000Budget

 

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)

 

 

 

Need Help

4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    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's avatar
      Rasmus7700
      Frequent Visitor
      2 years ago

      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.... 

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.