In the attached file , I want Cumulative Final to Collect to be the cumulative of Total Project Collections and Final To Collect
For Ex: In 2020 Dec, Cumulative Final To Collect = Total Project Collections+ Final To Collect for Dec 2020 = 20+0= 20
In 2021 Jan, Cumulative Final To Collect = Total Project Collections+ Final To Collect for Dec 2020 +Final To Collect for Jan 2021 = 20+0+10 = 30
In 2021 Feb, Cumulative Final To Collect = Total Project Collections+ Final To Collect for Dec 2020 +Final To Collect for Jan 2021+Final To Collect for Feb 2021= 20+0+10+3 = 33
Cumulative Final To Collect Prep =
VAR vTable =
ADDCOLUMNS(
SUMMARIZE( 'Date', 'Date'[Year], 'Date'[MonthName] ),
"@Total", [Final To Collect]+[Total Project Collections]
)
RETURN
SUMX( vTable, [@Total] )
and for the final result.
Cumulative Final To Collect Result =
CALCULATE( [Cumulative Final To Collect Prep],
FILTER( ALLSELECTED('Date'),
'Date'[Date] <= MAX( 'Date'[Date] )
)
)
In context of your table visual it’s returning exactly the same result as SUMX would do over the vTable, except for the Total Row where SUMX will return an incorrect value. To correct that I used AVERAGEX instead, to illustrate why that works I’ve added an image below showing what each AVARAGEX is returning in the Measure. I hope that clarifies it better.
I can see the solution you had given first time is some what similar.
The performance improvement in Antriksh formula ,is it because he is not using Summarize/filtered the dates to only required dates?
VAR vTable =
ADDCOLUMNS(
SUMMARIZE( ‘Date’, ‘Date’[Year], ‘Date’[MonthName] ),
“@Total”,
[Final To Collect]+[Total Project Collections]
)
RETURN
This one is 4.5x faster and runs in ~16ms compared to previous one which runs in ~74ms. Since DAX relies on Cache, after the second refresh of the visual it will start running in ~12ms.
Antriksh 2 =
VAR MaxMonthYear =
MAX ( 'Date'[MonthnYear] )
VAR vTable =
CALCULATETABLE (
ADDCOLUMNS (
DISTINCT ( 'Date'[MonthnYear] ),
"@Total",
CALCULATE (
MIN (
SUM ( 'To Collect'[To Collect] ),
CALCULATE (
VAR MaxDate =
MAX ( 'Date'[MonthnYear] )
VAR Result =
CALCULATE (
SUM ( 'To Collect'[To Collect] ),
'Date'[MonthnYear] <= MaxDate,
REMOVEFILTERS ( 'Date' )
)
- CALCULATE ( SUM ( 'Unapplied Receipts'[AdvPayment] ), REMOVEFILTERS ( 'Date' ) )
RETURN
Result,
FILTER (
ALL ( 'Date'[MonthnYear] ),
'Date'[MonthnYear] <= MAX ( 'Date'[MonthnYear] )
)
)
)
)
+ CALCULATE ( SUM ( 'To Collect'[Collections] ) )
),
ALLSELECTED ( 'Date' )
)
RETURN
SUMX ( FILTER ( vTable, 'Date'[MonthnYear] <= MaxMonthYear ), [@Total] )