Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 L...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Please find the sample, thanks in advance for help:

 

v-danhe-msft's avatar
v-danhe-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

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