CALCULATETABLE( SUMMARIZE( aggregation by def

[benjaminzaidner]

"Hey, I need some assistance, please. I have the following formula. I also have another dataset, which is a file of shipment allocations by trucks and customers. I want to implement a condition where if a customer doesn't have an order totaling $800 on a specific date, then that order will be added to the next order. In the next order, it will again check if it reaches $800, and this can take into account the data from the previous order if there was one. So, I created a pivot for the original dataset, and now I'm planning to filter the data by customer and date, so I can identify when the condition occurs. Essentially, I want to reduce the number of shipments, so it only happens if the order reaches the threshold of $800. However, it's important to note that if there's an order for less than $800, we wait for accumulation until such an order is formed.

I am currently conducting the check on a specific month in the year 2024

like this , 

customerShipments =
CALCULATETABLE(
SUMMARIZE(
'TABLE1',
'TABLE1'[customer],
'TABLE1'[date],
"Qty", SUM('TABL1'[AMOUNT])
),
'TABL1'[Year] = 2024,
'TABL1'[Month] = 2,
''TABL1'[AMOUNT] > 0
)

[benjaminzaidner]

Please, I have attached a link to the file, thanks for wanting to help.

https://drive.google.com/file/d/1VjC7gl7L5T4MPWYfwvP55QEYyLIzAgZx/view?usp=sharing

[v-shex-msft]

HI @benjaminzaidner,

After I test and coding formula on sample file based on your conditions, I find this requirement is a recursive calculation which not able to be achieved in Dax expressions. 

You can simply to check if current value is large than the target, but it is hard to use Dax to find the dynamic cumulative start range. (this should base on previous calculations)

If you want to know more about recursion calculation, you can take a look at Greg’s blog:

Previous Value (“Recursion”) in DAX 

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @benjaminzaidner ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng