quarter value disstribution in a monthly column

[akiaie]

Hi,

In power BI I have a table with column of 12 months of the year. in front of each month there is a value for business plan. however value for first month of each month is for whole quarter and Value for month 2 and month 3 of each month is zero. I want to create a measure which distributes the quarter value of business plan which is stored in month 1 of each quarter to be distributed on each month of the quarter equally. like below, I want the measure to return the value in Monthly column.

any help would be appreciated. I am new in DAX. 🙂 

Year	Month	Business Plan	mothly
2023	1	30000	10000
2023	2	0	10000
2023	3	0	10000
2023	4	60000	20000
2023	5	0	20000
2023	6	0	20000
2023	7	99000	33000
2023	8	0	33000
2023	9	0	33000
2023	10	33000	11000
2023	11	0	11000
2023	12	0	11000

[Jihwan_Kim · Accepted solution]

Hi,

I assume your datamodel has a calendar table.

Please check the below picture and the attached pbix file.

Monthly plan measure: =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            'Calendar',
            'Calendar'[Year],
            'Calendar'[Quarter],
            // 'Calendar'[Month name],
            'Calendar'[Month number]
        ),
        "result",
            CALCULATE (
                DIVIDE (
                    SUMX (
                        FILTER (
                            'Business Plan',
                            VAR _currentyear =
                                MAX ( 'Calendar'[Year] )
                            VAR _currentquarter =
                                MAX ( 'Calendar'[Quarter] )
                            RETURN
                                'Business Plan'[Year] = _currentyear
                                    && ROUNDUP ( DIVIDE ( 'Business Plan'[Month], 3 ), 0 ) = _currentquarter
                        ),
                        'Business Plan'[Business Plan]
                    ),
                    3
                )
            )
    ),
    [result]
)