DAX measure for calculating open values between two dates - Power Pivot

[pmc0861]

Hi,

New to Power Pivot and DAX so please excuse if this isn't the correct place to post or my question is utterly ridiculous.

I have a table that has a list of purchase orders. The table includes two date columns, one for the date the PO was opened and the other for the date the PO was closed.

I also have a dimension table that lists months

What I'm wanting to do is create a pivot table using power pivot that shows the open value of purchase orders at a point in time determined by the mth and lists the open values as follows (open value is sum of Amount where open_date <= Month & close_date > Month):

I'm unsure how to define this in a measure in Power Pivot as I'm not sure how to link it to the month table. I've tried a few things and even just trying something simple like only using one date I can't work out... 

Ideally I'd like a measure that looks at both dates or if recommend a better way to be looking at this.

Any help would be appreciated as again, I'm very new to this.

thanks!

[pmc0861]

Thanks. This definitely got me moving in the right direction (stepping through this one part at a time).... I've got my DAX expression now as:

CALCULATE(SUMX(FILTER(PO_Committed,PO_Committed[AddedMth]<=max(Month_Tbl[Month])),[OrigCost])

)

Note that column names are a little different from my original post as original post was simplified to clarify the issue I was having.

For some reason, the above DAX only gives me items added in that month rather than in that month or earlier despite using <= in the filter. Any idea why this would be happening?

[pmc0861 · Accepted solution]

OK.... scratch the above. I had a relationship set with the date table that I've now marked as inactive and its giving me what I expected.

[amitchandak]

@pmc0861 , seems similar to the HR use case - https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU