Assistance Needed: Creating Year-Month Columns and Sum in Power BI

[hsp1992]

Hello,

I am having difficulty creating columns (or maybe a measure) in Power BI.

I have a dataset with Unique UserName and Terminated Date.

I want to create a table that has a Year-Month column and counts the number of terminated people in that Year-Month.

Additionally, I want to build a column that sums the terminated counts for the last 12 months. For example, if the month is May 2024, I want to count the number of terminations from May 2023 to May 2024.

I want to do this for all months. For instance, if the month is June 2024, I want to count the terminations from June 2023 to June 2024.

Can you please help me with a way to build this column?

Thank you so much.

[v-kaiyue-msft · Accepted solution]

Hi @hsp1992 ,

Based on your description, I created these example data.

1. create measure to calculate the count for the last 12 months.

MEASURE =
VAR _start =
EOMONTH ( MAX ( 'Table'[Terminated Date] ), 0 )
VAR _end =
EOMONTH ( MAX ( 'Table'[Terminated Date] ), -13 ) + 1
RETURN
CALCULATE (
COUNT ( 'Table'[Unique UserName] ),
FILTER (
ALL ( 'Table' ), FILTER (
'Table'[Terminated Date] <= _start
&& 'Table'[Terminated Date] >= _end
)
)

2. Put the appropriate fields in the table visual object. The date column retains the year and month hierarchy, and the summarise method for username selects count.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[v-kaiyue-msft · Accepted solution]

Hi @hsp1992 ,

Based on your description, I created these example data.

1. create measure to calculate the count for the last 12 months.

MEASURE =
VAR _start =
EOMONTH ( MAX ( 'Table'[Terminated Date] ), 0 )
VAR _end =
EOMONTH ( MAX ( 'Table'[Terminated Date] ), -13 ) + 1
RETURN
CALCULATE (
COUNT ( 'Table'[Unique UserName] ),
FILTER (
ALL ( 'Table' ), FILTER (
'Table'[Terminated Date] <= _start
&& 'Table'[Terminated Date] >= _end
)
)

2. Put the appropriate fields in the table visual object. The date column retains the year and month hierarchy, and the summarise method for username selects count.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[hsp1992 · Accepted solution]

First of all, thank you so much for helping me with this. Your assistance has really helped me to resolve the issue.

Here is the image of the data that I have right now. What I want to do is add the value of the past 12 months. And I have the Expected Value in the below image.

For example:

For August 2024, I should have a value of 45 because it includes the sum of the values from August 2023 to August 2024. Another example, for January 2024, I should have a value of 44.

Can you help me to add the values based on the past 12 months?

Thank you so much for all your help.

[hsp1992]

I think I resolved it with below query

Rolling12MonthTerminationCount =

VAR CurrentYearMonth = MonthlyTerminatedEmployees[YearMonthSort]

VAR CurrentDate = DATE(MonthlyTerminatedEmployees[Year], MonthlyTerminatedEmployees[Month], 1)

VAR StartDate = EOMONTH(CurrentDate, -12) + 1

RETURN

CALCULATE(

    SUM(MonthlyTerminatedEmployees[Terminated Count]),

    FILTER(

        ALL(MonthlyTerminatedEmployees),

        MonthlyTerminatedEmployees[YearMonthSort] >= YEAR(StartDate) * 100 + MONTH(StartDate) &&

        MonthlyTerminatedEmployees[YearMonthSort] <= CurrentYearMonth

    )

)

Thank you so much!