Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
akiaie
New Member

quarter value disstribution in a monthly column

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

YearMonthBusiness Planmothly 
202313000010000
20232010000
20233010000
202346000020000
20235020000
20236020000
202379900033000
20238033000
20239033000
2023103300011000
202311011000
202312011000
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I assume your datamodel has a calendar table.

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1685782609920.png

 

Jihwan_Kim_1-1685783730378.png

 

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]
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

Hi,

I assume your datamodel has a calendar table.

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1685782609920.png

 

Jihwan_Kim_1-1685783730378.png

 

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]
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors