Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AlishaN
Regular Visitor

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:

AlishaN_0-1648237121533.png

 

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:

AlishaN_1-1648237399665.png

AlishaN_2-1648237423436.png

 

AlishaN_3-1648237437877.png

AlishaN_4-1648237445876.png

In table format:

AlishaN_5-1648237497293.png

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:

AlishaN_6-1648237637708.png

In table format:

AlishaN_7-1648237683080.png

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:

AlishaN_8-1648237773914.png

 

Month over Month perspective:

AlishaN_9-1648237801610.png

 

Week over week (end of week) perspective:

AlishaN_10-1648237824831.png

 

Any help you could offer would be greatly appreciated! Thank you! 

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

Hi @AlishaN ,

 

You may try this solution.

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

vcazhengmsft_0-1648544187507.png

 

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.

vcazhengmsft_1-1648544187510.png

 

vcazhengmsft_2-1648544187514.png

 

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!

View solution in original post

2 REPLIES 2
v-cazheng-msft
Community Support
Community Support

Hi @AlishaN ,

 

You may try this solution.

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

vcazhengmsft_0-1648544187507.png

 

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.

vcazhengmsft_1-1648544187510.png

 

vcazhengmsft_2-1648544187514.png

 

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

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors