Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
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.
@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
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |