Running total too slowly

[Anonymous]

Hi,

I've a simple model, Iput a pbix file https://1drv.ms/u/s!Amd7BXzYs7AVhBEkwk7KkWhVqete?e=sireeV 

I'm trying to calculate runnig total till last month between two selected dates, as example:

Year	month	day	time	running total( classic)	Prior till last month (expected)
2019	1	12	12	12	0
2019	1	14	15	27	12
2019	2	12	18	45	27
2019	2	13	21	66	45
2019	2	14	24	90	66
2019	5	15	27	117	90
2019	5	16	30	147	117
2019	5	17	33	180	147
2019	12	18	36	216	180
2020	4	19	39	255	216
2020	4	20	42	297	255
2020	4	21	45	342	297
2020	5	22	48	390	342
2020	5	23	51	441	390
2020	7	24	54	495	441
2020	8	25	57	552	495
2020	9	26	60	612	552
2020	12	27	63	675	612

I tried, but prior to date is too slowly, how to optimize it? When I do a search on net, may be I need to use RANKX but I don't to figure out how to use it?

prior to date = var _maxdate=CALCULATE(max('Date'[Date]), ALLSELECTED(('Date'[Date])))

var _mindate=CALCULATE(min('Date'[Date]), ALLSELECTED(('Date'[Date])))

return

calculate(sum('Table (2)'[Time ]), filter(all('Date'), 'Date'[Date]<=max('Date'[Date])))+0

How to calculate Prior till last month (expected)? 

[Anonymous]

HI @Anonymous,

Have many records your calendar table stored? Account to your description, It seems like you use the cumulative calculation expression on the calendar table to summary table2 records.
For this scenario, it means each calendar row will be looping with table 2 records. (the total looping row count = calendar row count * [2 * selected calendar row count + table2 row count ] )

In addition, I modify the formula to reduce the calculation row counts, you can also try to use the following formula if helps:

prior to date =
VAR currDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table (2)'[Time ] ),
        FILTER ( ALLSELECTED ( 'Table (2)' ), 'Table (2)'[Date] <= currDate )
    ) + 0

Reference link about Dax iterator performance:

Optimizing nested iterators in DAX - SQLBI

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , I tried like this

Measure = calculate(sum('Table (2)'[Time ]), filter(ALLSELECTED('Date'), 'Date'[Date]<=eomonth(max('Date'[Date]),-1)))

Data is very small, I do not face any performance issue

what you showing is not last month till date

The file is attached after signature