topic Re: DAX measure for calculating open values between two dates - Power Pivot in DAX Commands and Tips

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

pmc0861 — Tue, 20 Dec 2022 02:34:04 GMT

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!

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

amitchandak — Tue, 20 Dec 2022 03:05:19 GMT

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

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

pmc0861 — Tue, 20 Dec 2022 04:53:07 GMT

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?

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

pmc0861 — Tue, 20 Dec 2022 05:29:45 GMT

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.