Date table - how to sort my relationship out

[abihoughton27]

Hi,

i have created a dates table for my tool but i am not sure if the relationship is right.

i have created some graphs from my benefits table when I use my date table it links to the delivery milestone dates and not the benefits table – not sure if that makes sense

The graph on the left is using my date table (where I can sort the months by FY this is somehow using the date in the delivery milestone table) and the right using the date field in the benefits table (this has the right figures) how can i use the date from the date table and it use the date from the benefits table? i have put the relationship in an image below

[v-henryk-mstf]

Hi @abihoughton27 ,

According to the formula provided by @amitchandak , the logic of the last line is to find the sum of the value fields in the filtering time range.

Try the above formula to see if it works for you. If there is still a problem, please provide the error message and test the data model (remove sensitive information), and I will answer you as soon as possible. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[abihoughton27]

Hi @v-henryk-mstf 

Thanks for explaining that to me yes it works i just wanted to understand the formula so i can learn for next time.

Thanks again

[amitchandak · Accepted solution]

@abihoughton27 , Create a measure and use date from date table, In this model, you will not be able to join the two

measure =

var _min = minx(allselected('Date'), 'Date'[Date])
var _max = maxx(allselected('Date'), 'Date'[Date])
return
CALCULATE(sum('Milestoen benefit'[Value]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max))

[abihoughton27]

Thanks for the response - would you be able to explain the last line?