cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FrankMcQ Frequent Visitor
Frequent Visitor

Build a column based on a date filter from another table

Hi

 

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:

Date                  Amount

01/11/2017       20.87

01/12/2017       32.31

...

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
FrankMcQ Frequent Visitor
Frequent Visitor

Re: Build a column based on a date filter from another table

Perfect, Many thanks!

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

Re: Build a column based on a date filter from another table

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.

 

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

Highlighted
Super User II
Super User II

Re: Build a column based on a date filter from another table

Hi @FrankMcQ 

try

Invoices in Q1 = CALCULATE(SUMX(FILTER('Invoices', 
AND('Invoices'[Invoice Date] >= SELECTEDVALUE('Milestones'[Start Date]), 'Invoices'[Invoice Date] < SELECTEDVALUE('Milestones'[Q1]))),  
'Invoices'[Amount]))

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


do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

FrankMcQ Frequent Visitor
Frequent Visitor

Re: Build a column based on a date filter from another table

Perfect, Many thanks!

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors