topic Cumulative value between dates of each row in DAX Commands and Tips

Cumulative value between dates of each row

egabriel — Mon, 05 Sep 2022 12:44:13 GMT

Hi there, need some assistance with calculating cumulative values.
I have a table with the billings of a certain period and I need to add the values that are between the dates of each row.

According to the excel model, I use the two dates of each line as a calculation condition.

I found a solution, but when I use a larger database, BI crashes

Memory error: Memory Allocation failure . Try simplifying or reducing the number of queries.

Calculation

Accumulation =

VAR Due date = Invoicing[Due date]

VAR Issue = Invoicing[Issue]

VAR Result =

CALCULATE([SumValues],

ALL(Invoicing),

Invoicing[Issue]<=Due date,

Invoicing[Due date] >=Issue

)

Return Result

Can you offer any advice to fix this please?

Re: Cumulative value between dates of each row

Jihwan_Kim — Mon, 05 Sep 2022 13:25:49 GMT

Hi,

I am not sure whether I understood your question correctly, but please try the below whether it suits your requirement. It is for creating a calcualted column.

Accumulation CC = SUMX ( FILTER ( Invoicing, Invoicing[Due date] >= EARLIER ( Invoicing[Issue] ) && Invoicing[Issue] <= EARLIER ( Invoicing[Due date] ) ), Invoicing[Value] )

Re: Cumulative value between dates of each row

egabriel — Mon, 05 Sep 2022 17:59:09 GMT

Hello, your calculation is correct, but when I added 2 more conditions and went to update through onedrive, it presented the memory error.
Even though it's a small base of 24mb.

Re: Cumulative value between dates of each row

v-yadongf-msft — Tue, 06 Sep 2022 08:30:12 GMT

Hi egabriel ,

Refresh is a background operation that can occur when two conditions are met:

There is sufficient memory
The number of supported concurrent refreshes for the Premium capacity is not exceeded

When the conditions are not met, the refresh is queued until the conditions are favorable.

For a full refresh, recall that at least double the current dataset memory size is required. If sufficient memory is not available, then the refresh cannot commence until model eviction frees up memory - this means delays until one or more datasets becomes inactive and can be evicted.

For more information, please refer to: Optimize Microsoft Power BI Premium capacities - Power BI | Microsoft Docs

Please check if there is any remaining memory in the workspaces.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.