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
BabyBinki821
Helper I
Helper I

Please help before I jump off a bridge :( I need to do cumulative total

Hi

 

I have spent a day on this and going crazy.

 

I need to create cumulative sum of accounts and put it in week end and should look like the graph down below. The other part of this is that I cannot have the graph reset to 0 to whatever date is picked in the splicer as the earliest. In terms of my data table I have accounts, when they made their first order and have a date table that week end. I am utterly lost can't even get the column to sum up (created a new column and put the value 1 in there).

 

BabyBinki821_1-1649604868301.png

 

 

 

 

BabyBinki821_0-1649604715355.png

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @BabyBinki821 

Please don't!

At least try this before 😅
I assume you are using Date[End of Week] column in you visual and the Date[Date] column is in the slicer. Then you may try

Cumulate Ordering Accounts =
VAR FirstDateInSlicer =
    MIN ( 'Date'[Date] )
VAR LastDateInFiltet =
    MAX ( 'Date'[Date] )
VAR CurrentDateInFilter =
    MAX ( 'Date'[Last Date of Week] )
RETURN
    CALCULATE (
        SUM ( Accounts[Assigned Value] ),
        ALLEXCEPT ( 'Date', 'Date'[Date] ),
        Accounts[FIRST ORDER] >= CurrentDateInFilter
    )

View solution in original post

8 REPLIES 8
BabyBinki821
Helper I
Helper I

Do you know how I would put this in a column so I can % growth and other calculations?

@BabyBinki821 

The calculated column cannot interact with  the filter context. Can you please explain what exactly do you emeant to achieve?

A lot of the charts that I need to use have two main variables - cumulative accounts & orders with week end as the main time function. So a couple of things I need to do show cumulative account growth week over week, show % of cumulative accounts by region over time, show weekly average of orders/cumulative accounts for that week. So in my head I though if I had a column with the cumlative values it would be easy to do some of those charts below that I already have made in Excel.

 

BabyBinki821_0-1649640310832.pngBabyBinki821_1-1649640395164.pngBabyBinki821_2-1649640420253.png

 

tamerj1
Super User
Super User

Hi @BabyBinki821 

Please don't!

At least try this before 😅
I assume you are using Date[End of Week] column in you visual and the Date[Date] column is in the slicer. Then you may try

Cumulate Ordering Accounts =
VAR FirstDateInSlicer =
    MIN ( 'Date'[Date] )
VAR LastDateInFiltet =
    MAX ( 'Date'[Date] )
VAR CurrentDateInFilter =
    MAX ( 'Date'[Last Date of Week] )
RETURN
    CALCULATE (
        SUM ( Accounts[Assigned Value] ),
        ALLEXCEPT ( 'Date', 'Date'[Date] ),
        Accounts[FIRST ORDER] >= CurrentDateInFilter
    )

@BabyBinki821 

Also I forgot to mention that this a measure not a calculated column

This worked!!! But now I have balnk in all my time splicers 🤣

@BabyBinki821 

Can you please share some screenshots of you data and data model as well as the report and slicers

You are a life saver! The blanks were caused from how I had the relationship set with the data tables. I finally figured it out how to fix it.

 

But you sir saved my life.

 

BabyBinki821_0-1649622489122.png

 

 

 

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