Forum Discussion

FrankMcQ's avatar
FrankMcQ
Frequent Visitor
6 years ago
Solved

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      ...
Need Help
  • FrankMcQ's avatar
    FrankMcQ
    6 years ago

    Perfect, Many thanks!

az38's avatar
az38
Community Champion
6 years ago

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's avatar
FrankMcQ
Frequent Visitor
6 years ago

Perfect, Many thanks!