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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
hsp1992
Regular Visitor

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

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.

 

2 ACCEPTED SOLUTIONS
v-kaiyue-msft
Community Support
Community Support

Hi @hsp1992 ,

 

Based on your description, I created these example data.

vkaiyuemsft_0-1723081022944.png


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.

vkaiyuemsft_1-1723081067273.png

 

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.

View solution in original post

hsp1992_0-1723146282150.png

 


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.

hsp1992_1-1723146477293.png

 


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.

View solution in original post

3 REPLIES 3
v-kaiyue-msft
Community Support
Community Support

Hi @hsp1992 ,

 

Based on your description, I created these example data.

vkaiyuemsft_0-1723081022944.png


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.

vkaiyuemsft_1-1723081067273.png

 

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_0-1723146282150.png

 


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.

hsp1992_1-1723146477293.png

 


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.

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!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.