Calculating cumulative values with drillable dates

[AlishaN]

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! 

[v-cazheng-msft · Accepted solution]

Hi @AlishaN ,

You may try this solution.

1 Use Year, Month, Week columns from Date Table as shared axis

2 Use the following Measure to calculate cumulative total transactions

Cumulative Total Transactions =
CALCULATE (
    [Total Transactions],
    FILTER (
        ALL ( 'Date Table' ),
        'Date Table'[TRANS_Dates] <= MAX ( 'Date Table'[TRANS_Dates] )
    )
)

Then, the result should look like this.

Also, attached the pbix file as reference.

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

[Whitewater100]

Hi:

Can you make your relationship between Events and Date Table active, not inactive? Then you need not use USERELATIONSHIP.

If you have already marked your date table as such, you can use the time intell measures Power BI offers. (Date table has no missing dates and spans entire time from Jan 1 thru end of Year).

Normally your transaction table would be on the many side, but you must only have one transaction per day, the way it appears - which is OK.

I don't see how you are breaking out transactions by completed, successful, etc as no field in the tables have that status(in the images).

Usually you can count transactions by using this measure:

Transactions = COUNTROWS(Trans_Events).

Then you usually can do this, let's say for cancelled transactions:

Cancel Trans = CALCULATE[Total Transactions], 'Table[Column name] = "Cancelled") and similiar for all the other types of transactions.(Successful, Complete, etc) but a staus field would usually help with that in your Trans_Events table.

Your cumulative total for any year and any measure now can be:

 Calculate[Total Transactions], DATESTYD('Date Table'[Trans_Dates])

Your cumulative lifetime measure can be 

 **bleep** LT = CALCULATE([Total Transactions], FILTER(ALLSELECTED('Date Table'[Trans_Dates]), 
                  'Date Table'[Trans_Dates] <= MAX('Date Table'[Trans_Dates])
)

Your week and month should be fine just using your measures as values and pulling the appropriate field(week, month end, etc) from Date table in the X axis.

I hope this helps and if you want a quick review of your model, I can do that.