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 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?

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

3 Replies

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

    Hi Anonymous,

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

     

    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