Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Diminishing running total with two date values

I am attempting to analyze cohorts of opportunities by the Created Month (rows), and then evaluate month-by-month (columns) after creation to show how many are still open from the original cohort.

This is the indended output, where on the left we see total created by create date.  On the right we see how many are still open at the end of each month.  This is defined as the Close Date is greater than the calendar date.

 

Going from Excel to PowerBI, I can get this far.  The only issue is the counts go back to the beginning of the period.  For example, February in the rows with Jan in the columns should be null since anything created in February would not have a value in January.

I was able to accomplish the above with the following.  However, I can't figure out how to zero out the intersections where Create Date is less than the Calendar Date.

Count Still Open = 
 calculate(
     count('Opp table'[Opportunity ID]),
     filter(ALLSELECTED('Calendar'[Date]),
     'Calendar'[Date]>=min('Calendar'[Date])
     )
 )

 

The Create Date does not have a relationship to the calendar table since there is already a relationship between Close Date and the Calendar table.

 

Any changes I make to the relationship to help with Create Date impacts the Close Date relationship, and vice versa.  I'm not sure where to go from here.

 

  • For your reference.

     

    I made 'Sample DATA' and use it.

     

    Step 1: I copy 2 columns and rename them.

    Step 2: I unpivot 2 columns.

    Step 3: I add 'Flag' column and change type.

    Step 4: I add calendar table and add relationship.

    Step 5: I make a measure and add matrix.

     

    Sample DATA:

    Opportunity ID Create Date Close Date
    A001 2023/01/01 2023/10/25
    A002 2023/01/16 2023/07/21
    A003 2023/01/23 2023/05/13
    B001 2023/02/03 2023/04/03
    B002 2023/02/18 2023/12/23
    B003 2023/02/27 2023/08/03
    C001 2023/03/09 2023/06/01
    C002 2023/03/13 2023/04/01
    C003 2023/03/20 2023/11/18

2 Replies