Monthly table with sum of values that are effective during a specific month

[lozg]

Hi there,

I am trying to create a calendar month table that sums the value of employee pay and benefits that are effective during specific month periods.

I have a database table - that contains a row per employee per active record - with an effective date and, if it has since ended (and been replaced by a new value on a different row) then an end date.

I have built the setup in Excel. However, I need to set this up in Power BI and I'm struggling with identifying the appropriate month to sum the data by, where there isn't an explicit row for each month that the pay or benefit item is effective (i.e. if salary is effective from January to July - identifying that it is effective in February, March, April, May and June as well as January).

File here 

Any help in working out how to setup a table like in the Excel, based on the Database data, would be very gratefully appreciated!

[v-yanjiang-msft · Accepted solution]

Hi @lozg ,

According to your description, I download your sample and here's my solution.

1.Create a new Calendar table. Don't make relationship between the two tables.

Calendar =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Database'[Effective Date] ), DATE ( 2022, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] )
)

2.Create a measure.

Measure =
VAR _Value =
    SUMX (
        FILTER (
            'Database',
            EOMONTH ( 'Database'[Effective Date], 0 ) <= MAX ( 'Calendar'[Date] )
                && (
                    EOMONTH ( 'Database'[End Date], 0 ) >= MAX ( 'Calendar'[Date] )
                        || [End Date] = BLANK ()
                )
        ),
        'Database'[Value]
    )
VAR _T =
    SUMMARIZE (
        'Calendar',
        'Calendar'[Year],
        'Calendar'[Month],
        "Value",
            SUMX (
                FILTER (
                    'Database',
                    EOMONTH ( 'Database'[Effective Date], 0 ) <= MAX ( 'Calendar'[Date] )
                        && (
                            EOMONTH ( 'Database'[End Date], 0 ) >= MAX ( 'Calendar'[Date] )
                                || [End Date] = BLANK ()
                        )
                ),
                'Database'[Value]
            )
    )
RETURN
    IF ( HASONEVALUE ( 'Calendar'[Month] ), _Value, SUMX ( _T, [Value] ) )

3.Put Month column from Calendar table in Matrix X-axis, Category L1 in Y-axis, and the measure in Values, get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[lozg]

Thank you so much kalyj!
That is a thing of beauty and exactly what I needed!

I updated the calendar to also have a month in "mmmm" format - however if I use this in the table, it now displays in alphabetical order (April, August, December etc).
Is there an easy adjustment for the month name to appear in chronological order?
Additionally, is there an easy way to adjust the Category L1 ordering to display as Pay, Benefits, Short-Term Incentives and then Long-Term Incentives?

[v-yanjiang-msft]

Hi @lozg ,

Sorry for late reply, if you want to sort the Month in chronological order, you can create a MonthNo column, then sort the Month column by MonthNo column.

For example:

Calendar =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Database'[Effective Date] ), DATE ( 2022, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "MonthNo", MONTH ( [Date] )
)

Result:

For the Category L1 column, you can add a custom column in Power Query:

if [Category L1]="Pay" then 1 else if [Category L1]="Benefits" then 2 else if [Category L1]="Short-Term Incentives" then 3 else 4

Result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[lozg]

Thank you so very much kalyj!!
That is absolutely perfect and made my week!
I really appreciate all the help!

[amitchandak]

@lozg , refer if these can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Between Dates - Dates between
Tables
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...