Forum Discussion
DAX measure for calculating open values between two dates - Power Pivot
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!
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.
3 Replies
- amitchandak
Super User
pmc0861 , seems similar to the HR use case - https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU
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?
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.
