OFFSET Optimization for Large Dataset

[PBIUser9090]

I have a measure that is working as expected to determine the sum of the Total YTD Sales in the previous row in the Sales table when used on a partition (1 company key for 1 year('Product Sales'[Date])) of a larger dataset.  However, when the full dataset is loaded, the visual exceeds the available resources when the measure is used.  My dataset in full is approx 150 million records in the Product Sales table with the largest partition being about 30 million records.  Can this measure be optimized?

Sales =

VAR RelationshipV = ALL('Product Sales'[PointBreak],'Product Sales'[Date],'Product Sales'[Total YTD Sales], 'Product Sales'[Sales_key])

VAR PrevPayV =

CALCULATE(Sum('Product Sales'[Total YTD Sales]),

OFFSET(

        -1,

        RelationshipV,

        ORDERBY('Product Sales'[Date],ASC, 'Product Sales'[Total YTD Sales], ASC),

        KEEP,

        PARTITIONBY('Product Sales'[PointBreak])

),'Product Sales'[record_identifier] = "P4", Company[timeframe] = "YTD", REMOVEFILTERS('Product Sales'))

RETURN

PrevPayV

Thanks for your help!

[PBIUser9090]

I think I have narrowed down the issue to deployment in Analysis Services.  The measure is working as expected with a subset of the data in PowerBI with an import connection to a SQL database.  I've loaded the same subset of data to Analysis Services with the same OFFSET measure used in PowerBI, but when Analysis Services is connected to PowerBI it returns an error that there isn't enough memory to view the output of the measure.  I have attempted scaling up AS with no difference in outcome.  Is the OFFSET measure not available for use in Analysis Services yet?  Is there some other difference that could be causing the issue?  Thank you for any help!

[PBIUser9090]

@amitchandak - Thank you for your reply! In this case I am reversing YTD data into the change since the previous record by pointbreak sorted by date and Total YTD Sales amount.  I think that I need to somehow filter the measure to only iterate on the selected company_key while ignoring all other filters in the visual as the formula works with just one company_key of data, but I have not been able to do this.

[amitchandak]

@PBIUser9090 , For YTD you can use window

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc