Showing results for 
Search instead for 
Did you mean: 
abeinke2018 Frequent Visitor
Frequent Visitor

sumifs formula with related tables


 CalendarTable CropDataExcel Data


Hi All,


I am new to BI and have been using excel extensively, my issue is trying to sum amounts by day (given there is 000's of lines of data)


The types of formulas i have used are as below ...


Crop = CALCULATE(SUM(CropData[Kg]),FILTER(RELATEDTABLE(CropData),CropData[Date])<='Total Harvest'[Date]&&RELATEDTABLE(CropData),CropData[Date]>='Total Harvest'[Date])


Harvest = CALCULATE(SUM(CropData[Kg]),ALL('Total Harvest'[Date]))



What I am trying to get is the following (Excel)


=SUMIFS(L:L,B:B,">="&"Table Calander[Date]",B:B,"<="&"Table Calander[Date]",G:G,"GH 4")


Trying to reference the date field within the calendar table in order to summarise


Any help would be apreciated









v-yulgu-msft Super Contributor
Super Contributor

Re: sumifs formula with related tables

Hi @abeinke2018,



What is the intention of above formulas?


Besides, to illuatrate the formula you used in Excel, please show us the desired result.

=SUMIFS(L:L,B:B,">="&"Table Calander[Date]",B:B,"<="&"Table Calander[Date]",G:G,"GH 4")


How to Get Your Question Answered Quickly


Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
abeinke2018 Frequent Visitor
Frequent Visitor

Re: sumifs formula with related tables

I have this formula which gives me what I need, unless you know of a better way to do this ?


It sums up all the Kg's (000's of rows) for the location GH4 for each day


Field GH4 = CALCULATE(SUM(Field[GH4]),FILTER(ALL(Field),Field[Day]<='Total Harvest'[Date]&&Field[Day]>='Total Harvest'[Date]))




Helpful resources

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 96 members 1,542 guests
Please welcome our newest community members: