Rolling last 12 months in one table divided by a value in another table

[Anonymous]

Hi, new user here...I´m freaking out with this simple task.

I have two tables:

1 - First Table calls MB51 and was able to calculate a rolling sum for the last 12 months by company using the formula:

Rolling Sum 12Month =

CALCULATE( SUM(mb51[Value]),

DATESINPERIOD(mb51[Mês], MAX(mb51[Mês]), -12, MONTH))

2 - Second table calls MB5L and I need to divide a value there by the rolling sum in that specif month for the specific company.

Here is what I did:

     A-First I made the relationship bellow:

  B-Then, divided the columns 

TurnOver = ([Rolling 12 Month Positive]/[Total Inventário])

 C-As a result, I´m having the same total Rolling Sum per month and not in that specific month.

How can I have the real RollingSum by each Month for each column?

Any help here?

[v-xicai]

Hi @Anonymous ,

As  parry2k's suggestion, there are more details I will add. In the most common use case, dates of DATESINPERIOD function is a reference to the date column of a marked date table. You may create a calendar table first of all, then create relationship with your fact table on the date field.

Calendar= CALEANDARAUTO()

Then you may create measure like DAX below using the Calendar[Date] in function DATESINPERIOD . Note that the formula of [TurnOver] also could be create in measure instead of calculated column.

Rolling Sum 12Month =CALCULATE( SUM(mb51[Value]),DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -12, MONTH))

Finally, you may put Calendar[Date] into Rows box of Matrix visual instead of the date of your fact table.

Best Regards,

Amy 

Community Support Team _ Amy

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

[parry2k]

@Anonymous As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

Create relationship of both tables with this date table and in measure use this date table instead of specific date table from your transaction table.

Also in visualization, use columns from date dimension and everything will flow.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡