Forum Discussion

AlishaN's avatar
AlishaN
Regular Visitor
4 years ago
Solved

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 dril...
Cumulative Total
DAX
drill down
drill up
INACTIVE RELATIONSHIP
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    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's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

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.