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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
GRedhead
Helper I
Helper I

Need help getting total of a field over the last 6 months into a column on the same table

I am trying to get the total value of a field 'Closed Today' which is a count of records closed that day off all the records in a table over the past 6 months, and add the total into a new column, on each record.

 

01/11 total of 5
02/11 total 6 (as one is closed on the 02/11)

then six months after this date the total would be 1 as the first 5 are outside of the 6 months.

 

I have both the created on date, and the date 6 months before the created on available to me as fields.

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @GRedhead 

 

Sorry I'm a little confused about your expected result. Where does the column you want to add to? A calculated column or a column in a table visual? And what are the start dates of each 6 months period? Are these dates fixed or rolling? Can you please provide some sample data and expected result to make it more clear? Thanks very much.

 

Best Regards,

Community Support Team _ Jing Zhang

amitchandak
Super User
Super User

@GRedhead , Create a date table and join the date of date table with create date and close date and there will be active inactive relation, Use userelation to choose the join

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

or https://www.youtube.com/watch?v=e6Y-l_JtCq4

 

For the last 6 months use a formaul like this. use userelation as per need


Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-6,MONTH))
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-6,MONTH))
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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