Running Total Cohort DAX

Hi 1katznir 

I think you want to calculate the running total for each Start_OF_Month_Date by Cohort_Month.

And compare the expense with measure to calculate the min Cohort_Month for each Start_OF_Month.

I build two measures:

Running Total Booking Amount = 
SUMX (
    FILTER (
        ALL ( BOOKING_MONTHLY_COHORT ),
        BOOKING_MONTHLY_COHORT[Start_OF_Month_Date]
            = MAX ( BOOKING_MONTHLY_COHORT[Start_OF_Month_Date] )
            && BOOKING_MONTHLY_COHORT[Cohort_Month]
                <= MAX ( BOOKING_MONTHLY_COHORT[Cohort_Month] )
    ),
    BOOKING_MONTHLY_COHORT[Booking_Amount]
)

M.Cohort_Month = 
VAR _RunningTotalExpense_Amount =
    SUMX (
        FILTER (
            ALL ( BOOKING_MONTHLY_COHORT ),
            BOOKING_MONTHLY_COHORT[Start_OF_Month_Date]
                = MAX ( BOOKING_MONTHLY_COHORT[Start_OF_Month_Date] )
                && BOOKING_MONTHLY_COHORT[Cohort_Month]
                    <= MAX ( BOOKING_MONTHLY_COHORT[Cohort_Month] )
        ),
        BOOKING_MONTHLY_COHORT[Expense_Amount]
    )
VAR _COHORT =
    MINX (
        FILTER (
            ALL ( BOOKING_MONTHLY_COHORT ),
            BOOKING_MONTHLY_COHORT[Start_OF_Month_Date]
                = MAX ( BOOKING_MONTHLY_COHORT[Start_OF_Month_Date] )
                && [Running Total Booking Amount] >= _RunningTotalExpense_Amount
        ),
        BOOKING_MONTHLY_COHORT[Cohort_Month]
    )
RETURN
IF(NOT(ISBLANK(_COHORT)),IF(MAX(BOOKING_MONTHLY_COHORT[Cohort_Month])=_COHORT,_COHORT,BLANK()),BLANK())

Result is as below.

You can download the pbix file from this link: Running Total Cohort DAX

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.