How to calculate a more advanced trailing 12 months?

[Anonymous]

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

[v-yulgu-msft]

Hi @Anonymous,

Please follow this blog to provide more detailed information:

How to Get Your Question Answered Quickly

Best regards,

Yuliana Gu

[Anonymous · Accepted solution]

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]

Hi,

Share some data and also show the expected result.

[Anonymous]

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? 

[Ashish_Mathur]

Yes, share dummy data

[anandav]

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]

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

[davehus]

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]

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. ;(