Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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).
Solved! Go to Solution.
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
)
Do you know how I would put this in a column so I can % growth and other calculations?
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.
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
)
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 🤣
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.
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |