cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Calculate Running Total based on 2 Date Columns

Hi,

I have a Leavers list in Table having their Month of Leaving and Month of Joining.

* My Date table is linked to Month of Leaving.

I want a monthly trend / Running Total showing Sum of Leavers (who left in last 3 months and their month of joining is also in the last 3 months).

I am quite new to Power BI, can someone help me with the solution please?

1 ACCEPTED SOLUTION
Microsoft

Hi @Anonymous,

Based on my test, you could refer to below steps:

Create a date table and create relationship:

`Date = CALENDARAUTO()`

Create below calculated columns:

```a = CALCULATE (
SUM ( Table1[joiners]),
DATESINPERIOD('Date'[Date],'Table1'[Month],-3,MONTH))```
```b = CALCULATE (
SUM ( Table1[Leavers]),
DATESINPERIOD('Date'[Date],'Table1'[Month],-3,MONTH))```

Result:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Microsoft

Hi @Anonymous,

Could you please offer some sample data to have a test or post your data picture if possible?

Regards,

Daniel He

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

Microsoft

Hi @Anonymous,

Based on my test, you could refer to below steps:

Create a date table and create relationship:

`Date = CALENDARAUTO()`

Create below calculated columns:

```a = CALCULATE (
SUM ( Table1[joiners]),
DATESINPERIOD('Date'[Date],'Table1'[Month],-3,MONTH))```
```b = CALCULATE (
SUM ( Table1[Leavers]),
DATESINPERIOD('Date'[Date],'Table1'[Month],-3,MONTH))```

Result:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.