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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

How to calculate a more advanced trailing 12 months?

Dear all,

 

To simplify, I have a dataset in the format of

 

Column A     Column B          Column C                             Column D

Product        Business Unit     Date Sold (Month level)      Gross Margin

 

The products in column A are of tens of thousands different ones over the past years.

 

Also, not all products are sold in every month every year.

 

I have to figure out that for any given month, which products have the trailing 12 months cumulative gross margin of less than X dollars. Ideally, I should be able to trace in which business unit these particular products are sold.

 

I have been reading through the posts but seen no one in the similar situation.

 

Could anyone please help me out? 🙂

 

Best regards,

Desperate Analyst

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thank you everyone for replying 🙂 The issue was finally solved with a calculated column containing a combination of calculate, sum, filter, and datesinperiod

View solution in original post

9 REPLIES 9
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

 

Please follow this blog to provide more detailed information:

How to Get Your Question Answered Quickly

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you everyone for replying 🙂 The issue was finally solved with a calculated column containing a combination of calculate, sum, filter, and datesinperiod

Ashish_Mathur
Super User
Super User

Hi,

 

Share some data and also show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur and thanks for the reply!

 

Sharing data seems to be a done thing here?

 

Just curious about this, since many of us probably are solving issues that occurs at work.

 

Thus, do people just generate dummy data for seeking help? Cat Very Happy

Yes, share dummy data


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
anandav
Skilled Sharer
Skilled Sharer

Hi @Anonymous,

As @davehus mentioned you will need a date field.

If your Column C is in a date format, you can check this blog for help.

Anonymous
Not applicable

Thank you @anandav ! I will go through this post with thoughts and close the post if it helps 🙂

davehus
Memorable Member
Memorable Member

Hi - Do you have the date of sale by any chance? You could use a Dates in Period the get the TTM. Have you a sample file you could share?

Thanks
Anonymous
Not applicable

Thank you @davehus for the reply 🙂 The column C is in a date format, but they are only on a monthly level, that is 1.11.2016, 1.12.2016, 1.1.2017, etc. 🙂 

 

I think I have tested the Dates in Period function, but it doesn't seem to be right.

 

Because I have thousands of different products in column A, does this function automatically recognise that the products are different?

 

I calculated the annual rolling measure for this dataset by using group by, which is most probably correct. Then I compared it with the results I got from rolling measures, so far I haven't got them to match yet. ;(

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors