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

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. 

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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

    • 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)

         

         

         

         

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

    Here is a sample of the data structure, note:

    - Additional columns exists that provide further breakdowns as described above

    1katznir_2-1603002728492.png

    • Anonymous's avatar
      Anonymous
      Not applicable
      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 

      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