The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello!
I am having an issue with my cumulative sum formula. I just created a universal date table to connect and filter all of my other tables. The dates range 200 years. I now am trying to create a cumulative sum formula in my cost table, but am running into performance issues. Charts are taking very long to work when filters are put in place (I'm assuming that is due to size of my date table). Is there anyway to restrict the date table to enhance performance? I want to try and restrict the date table between a min and max date derived from my cost table. The cost table I am using for the cumulative sum only has 2 years worth of data. Here is an example of what I have tried:
VAR MinDate = min('CostTable'[Date])
VAR MaxDate = max('CostTable'[Date])
CALCULATE([CumulativeSum],
FILTER(
ALL('DateTable'[Date]),
AND('DateTable'[Date]>=[MinDate], 'DateTable'[Date].[Date] <= MaxDate))))
I have tried using allselected instead of all in the previous as well. I cannot share my data set, but if you can help it would be greatly appreciated. Thanks!
Edit: This is one of several formulas I have written so I have used all and allselected where I thought was needed.
To use ALLSELECTED you have to know how it works. Throwing functions (semi-randomly) at a formula without a deep understanding of how a function works is a sure way to fail.
Try this code:
[Cumul Sum] =
var __lastVisibleDate = MAX( CostTable[Date] )
return
CALCULATE(
SUM( CostTable[ColumnToSumOver] ),
CostTable[Date] <= __lastVisibleDate
ALL( DateTable )
)
// Here's a different version
// that will give you the correct VISUAL
// cumulative but only if the dates
// visible are consecutive.
[Cumul Sum] =
var __lastVisibleDate = MAX( CostTable[Date] )
var __firstVisibleDate =
CALCULATE(
MIN( CostTable[Date] ),
ALLSELECTED( DateTable )
)
return
CALCULATE(
SUM( CostTable[ColumnToSumOver] ),
CostTable[Date] <= __lastVisibleDate,
__firstVisibleDate <= CostTable[Date],
ALL( DateTable )
)
// And this version will give you
// the correct visual cumulative
// even for non-contiguous dates.
[Cumul Sum] =
var __lastVisibleDate = MAX( DateTable[Date] )
return
CALCULATE(
SUM( CostTable[ColumnToSumOver] ),
KEEPFILTERS(
DateTable[Date] <= __lastVisibleDate
),
ALLSELECTED( DateTable )
)
Hi @NR5046 ,
Use this measure
Running Total =
VAR MaxDate =
MAX ( 'Dates'[Date] ) -- Saves the last visible date in the slicer
VAR MinDate =
CALCULATE (
MIN ( Dates[Date] ),
ALLSELECTED ( Dates[Date] )
)
RETURN
CALCULATE (
[Total Sales],
'Dates'[Date] <= MaxDate
&& Dates[Date] >= MinDate
)
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
32 | |
20 | |
16 | |
15 |
User | Count |
---|---|
74 | |
31 | |
30 | |
24 | |
21 |