Calculating cumulative values with drillable dates
Hello 🙂 I would like to visualize transaction data in a bar and line chart. I want to show the cumulative total on the line and the transaction by period in the bars. I would like to be able to drill up and down. Model The relevant sections of my model are set up like this: Measures / DAX Used Total Completed Transactions = COUNT( Transactions[Transaction ID] ) Total Transactions = CALCULATE( [Total Completed Transactions], USERELATIONSHIP( TRANS_DateTable[TRANS_Dates], TRANS_Events[Initial Transaction Date] )) Total Successful Transactions = ([Total Transactions] - 'Totals Measures'[Total Cancelled] - 'Totals Measures'[Total Returned] - 'Totals Measures'[Total Defective]) Cumulative Total Transactions = CALCULATE('Totals Measures'[Total Transactions], USERELATIONSHIP( TRANS_DateTable[TRANS_Dates],TRANS_Events[Initial Transaction Date] ), TRANS_Events[Initial Transaction Date] <= MAX(TRANS_Events[Initial Transaction Date])) I have tried two methods to get my desired results. I should also note that my cumulative measure did use the FILTER and ALLSELECTED formulas at one point, but there was no difference in the results, so I removed it). Method 1: Fields: Shared Axis: TRANS_Dates Column Values: Total Transactions Line values: Cumulative Total Transactions Chart results: In table format: Issue with Method 1: The cumulative value does not calculate/display correctly. Method 2: Fields: Shared Axis: Initial Transaction Date Column Values: Total Transactions Line values: Cumulative Total Transactions Chart Results: In table format: Issue with Method 2: Cannot drill up or down. Desired results: A table and bar and line chart that displays totals for distinct periods as well as the cumulative total for that period. I also want to be able to slice by TRANS_Date and drill up and down for a range of viewpoints, like below (with contrived data): Year over Year perspective: Month over Month perspective: Week over week (end of week) perspective: Any help you could offer would be greatly appreciated! Thank you!
