Forum Discussion

1katznir's avatar
1katznir
Regular Visitor
5 years ago
Solved

Running Total Cohort DAX

Hi,   I have the following table: start_of_month_date (e.g. '2020-01-01') cohort_month (e.g. 0, 1, 2, 3, etc.) expense_type  paid_type expense_amount booking_amount I would like to create ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi 1katznir 

I download your pbix, but when I open it, it says I need to sign in snowflake or I can see nothing about your data model.

Can you show me a screenshot of your data model and tell me your calculate logic, and it may make it easier for me to understand your requirement.

Here I will give some advice.

I think you may try this measure:

 

Measure =
SUMX (
    FILTER (
        BOOKING_MONTHLY_COHORT,
        [START_OF_MONTH_DATE] = MAX ( [START_OF_MONTH_DATE] )
            && [COHORT_MONTH] = MAX ( [COHORT_MONTH] )
    ),
    [BOOKING_AMOUNT]
)

 

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. 

  • 1katznir's avatar
    1katznir
    Regular Visitor
    5 years ago

    Thanks for you reply, still not working as expected,

    please note that I would like to have the running total on the cohort month, so I believe I should use <= (please correct me if I am wrong)

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi 1katznir 

      Could you show me a sample of your table?

      From your data model I know you build a DIM Date Table and related two tables by Start_month_date column.

      But I am still confused about cohort month , if you give a sample table, it will make it easier for me to understand.

      Or you can share me a pbix file from you OneDrive for Business.

       

      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.