Events in progress help

[Aron_Moore]

So I thought I had this solved but it is not working quite right. Used this link.

Can anyone tell why the $125k line doesn't fall into the 1/1/2018 bucket? It was opened on 1/2/2018 so should show on week 1/1/2018.

I have the two tables, not joined/related, Open Value and Dates. Dates is for weekly bucketing and is a column of Mondays.

Measure

Weekly Amount = CALCULATE(SUM('Open Value'[Amount]),
 FILTER('Open Value','Open Value'[Open Date]<=CALCULATE(MAX(Dates[Week of]))),
 FILTER('Open Value','Open Value'[Net Due Date]>=CALCULATE(MIN(Dates[Week of]))))

Table of raw

Matrix using measure

Thanks!

[Aron_Moore · Accepted solution]

Think I figured it out. It is filtering the Open Date week because it's greater than the Week Of/Monday. Artificially padding seems to get it work as expected.

Is there a better solution?

Weekly Amount = CALCULATE(SUM('Open Value'[Amount]),
 FILTER('Open Value','Open Value'[Open Date]<=CALCULATE(MAX(Dates[Week of])+7)),
 FILTER('Open Value','Open Value'[Net Due Date]>=CALCULATE(MIN(Dates[Week of]))))

[v-huizhn-msft]

Hi @Aron_Moore,

Yes, you're right. Your solution is brief and clear. Please mark your solution as answer, so more people will get helpful information from here.

Thanks,
Angelia