I have a table of milestones:
Year Start Date End Date Q1 Q2 Q3 Q4
2018 01/10/2017 31/09/2018 01/10/2017 01/01/2018 01/04/2018 01/07/2018
2019 01/10/2018 31/09/2019 01/10/2018 01/01/2019 01/04/2019 01/07/2019
I also have a table of invoices:
I wanted to add columns to the financial milestones table to sum all of the invoices in that period... I'm using the following instruction:
Invoices in Q1 = CALCULATE(SUMX(FILTER('Invoices', AND('Invoices'[Invoice Date] >= 'Milestones'[Start Date], 'Invoices'[Invoice Date] < 'Milestones'[Q1])), 'Invoices'[Amount]))
However I keep getting the error:
Column 'Start Date', 'Q1' cannot be found or may not be used in this expression.
I'm struggling to understand why it can't find the columns - is it because I'm operating on the invoices table in the filter?
I guess I'm doing this incorrectly, any help would be gratefully received!
Go to Solution.
Perfect, Many thanks!
View solution in original post
These calculations will for the column, not for the measure. For measure is expects a single value. This means you have to take a min or max of another table.
Not sure why you creating a table like this you can use a calendar in powerbi to do this is a much better manner.
Invoices in Q1 = CALCULATE(SUMX(FILTER('Invoices',
AND('Invoices'[Invoice Date] >= SELECTEDVALUE('Milestones'[Start Date]), 'Invoices'[Invoice Date] < SELECTEDVALUE('Milestones'[Q1]))),
i think the problem occurs because DAX doesnt understand what exactly Milestone rows should be compared inside Invoices aggregations
do not hesitate to give a kudo to useful posts and mark solutions as solution
Join us in the third Triple A event!
It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.
Make sure you didn't miss any of the things that happened in the community in January!