cancel
Showing results for
Did you mean:
New Member

## 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 @Arunwats,

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 @Arunwats,

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.
New Member

Microsoft

Hi @Arunwats,

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.

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!