Cumulative Total within Date Range from 2 Date Tables

[awolf88]

Dear wizards,

Hope you guys can assist me once again, please. 

Pretty simple outlay: I have two tables, one for Sales, one for Open Orders. 

For tracking purposes, the Data for Open Orders (since it is changing on a constant basis), has 2 Date columns as the Data-Source always saves a copy of all open orders and attaches itself vertically by "INSERTED" date. So in short: the inserted Date from 07.04.2023 is a frozen overview of what all open orders looked like on that date, the inserted date from 09.04.2023 shows the very same status of open orders but at a later point in time. All in the same table.

Both tables are linked to a dim.Date table ("Calendar") via single Relationship ('Calendar' [Date] -> 'Sales' [Sales Date]) as well as 'Calendar' [Date] -> 'Open Orders' [Open Order Date]. 

I now have two simple overviews of Open Order advancement over time (projecting future month revenues) alongside current Sales total for running month. Easy as 1-2-3!

But here comes the tricky part: I would like to connect the sales table to the Inserted Date for a specific calculation: Since the Inserted Date (which has an inveral of every 2 days (so 07.04.2023, 09.04.2023, 11.04.2023, etc.), i would like to create a measure calculating the corresponding cumulative Sales Total up until that according date in order to add it to the same Matrix. 

I'm basically trying to calculate a cumulative total of Sales (with no direct relationship date-wise), where I'm saying: calculate month to date figures, where the Max Date is not the Max Date from the Sales Table, but from the Open Order Table instead (--> displayed "Inserted DATE") to which it has no relation to... 

The figures in blue would be the desired outcome.

So by Inserted Date 07.04.203: we would have accumulated $126.45 (equivalent of MTD Sales until then), etc.

Hope that makes sense and that one of you geniusses can point me in the right direction, please. 

I have demo data attached here for download purposes. 

https://www.dropbox.com/scl/fo/i6oeb9mvrypo4bawjwov1/h?dl=0&rlkey=vgd213f2hh34sf6aki42s62l7

Thank you so much for all your help in advance.

Best,

Alex

[Greg_Deckler]

@awolf88 You can either use an inactive relationship in conjunction with CALCULATE and USERELATIONSHIP or do it the No CALCULATE way. See this post for examples of both:

Solved: Re: No CACULATE challenge - Microsoft Power BI Community

[awolf88]

Hi Greg,

appreciate the rapid response and input!

Seems the userelationship is definitely the way to go for me. I've tried implementing the tips in the article and am now able to display the corresponding cumumlative Sales total by INSERTED Date (via userelationship). What I can't seem to understand though is that the provided Sales Revenues are only in the Month of April, yet it is showing me the same figures for every following month as well? 

The measure I'm using looks like such and I can't understand why it would be misinterpreting months:

Any ideas why? 

Thanks in advance!

Best,

Alex