FILTERS in a CALCULATE formula - can I use sum as filter?

[maurizio75]

Hi, I am still a learner of Power pivot and I am trying to creat pivot table with a formula that calculate a sum from a column starting from a specific date but it need to stop when a sum from a different column reach a determined value. Below I have atatched my model and pivto table. In blue I have highlighted my filter in the pivot table whereas in red are the measures and variable I need to use for my calculation. The formula should return the date that it is reached as soon as the daily sum of "Est GDH" reached the cumulative value of "GDH" in Blocks1, starting from the specific "planting date / manipulation date". The formula I have been trying looks like that: Estimate Harvest Date=CALCULATE(COUNTROWS('Time'),FILTER('Time',('Time'[Date])>=max(Blocks1[Planting date / manipulation date])&&SUM('Time'[Est GDH])>=max(Blocks1[GDH])))

 

[V-pazhen-msft]

@maurizio75 
"SUM('Time'[Est GDH])>=max(Blocks1[GDH]))
You cannot add sum in the expression like this, you can only refer a column. You may create temporary virtual table using summarize with required columns, then refer to the sum column in that table.

You can add all column in the temp table if needed. Should be something like the following. This measure is just a way to use summarize, which may not totally correct since I cannot test with only images. 

Estimate Harvest Date=
var temptable= summarize(Time, [HMS name], "Date", MAX(TIme[Date]), "ESTGDH", SUM('Time'[Est GDH]))Return COUNTX(filter(temptable, [Date]>=max(Blocks1[Planting date / manipulation date])&&[ESTGDH]>=max(Blocks1[GDH])),[Time[Date])

Best regards
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[maurizio75]

By the way thanks for the help so far....I can see how summarize would be useful to the case if I managed to create a virtual table

[maurizio75]

Can I create a virtual table in power pivot? or is it just a tool for power BI? I don't see the option in power pivot...I have tried to create a pivot table with a running total GDH by block but it becomes a monster.

[amitchandak]

@maurizio75 , You can use sum of measures in the filter, but for that, you have to force a context( group by).

using values of summarize

CALCULATE(COUNTROWS('Time'),FILTER(values('Time'[Date]) ,('Time'[Date])>=max(Blocks1[Planting date / manipulation date])&&SUM('Time'[Est GDH])>=max(Blocks1[GDH])))

here context of date( group by) is forced

[maurizio75]

@amitchandak , Thanks for your answer.....I am not quite sure if I undertand how to use summarize. The formual is still not working. I broke the formula and tested it piece by piece and the part that is not working is the last filter or SUM "SUM('Time'[Est GDH])>=max(Blocks1[GDH]))". For some reason the calculation doesn't stop as soon as it reached the GDH value but carries on until the end of my Time table. Any idea?