Running Total Cohort DAX

[1katznir]

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 a similar measure to: sum(booking_amount) over (partition by start_of_month_dateorder by cohort_month)

The report should contain filters by: expense_type and paid_tyoe so it would effect the new custom measure.

Updated: after creating this measure, I would like to create a new measure that shows the first cohort month when the condition: booking_amount - expense_amount > 0 then cohort_month else null (the business meaning is like the first month that booking running total is larger than expense amount)

*note: the custom measure be used in a report that support these breakdowns and also without the breakdowns (so just the totals per start_of_month_date), if that's not possible please note

[1katznir]

Here is a sample of the data structure, note:

- Additional columns exists that provide further breakdowns as described above

[Anonymous]

Hi @1katznir 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and the result you want or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous · Accepted solution]

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]

Hi @1katznir 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[1katznir]

pbix template file: 

https://ateranetworks-my.sharepoint.com/:u:/g/personal/nirk_atera_com/ES1H3lc2H65GpFz0dCp2GqYBF6TsQe...

[Anonymous]

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]

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]

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. 

[Fowmy]

@1katznir 

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn