cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
amitchandak Super Contributor
Super Contributor

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/

az38 Super Contributor
Super Contributor

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

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
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)