Log In
Register
Home
April 15, 2021
Offline
Hi everyone,
I’m working on creating a Power Pivot report to reflect a month-over-month trend of active orders. It seems straightforward, but I’m struggling to get the right counts, and I can’t figure out where I’m going wrong. Here’s my setup:
Data Setup:
- fact_orders: Contains purchase order and subcontract information (including award and end dates).
- dim_date: Date table.
- dim_org: Organizational hierarchy table.
- dim_contracts: Contains program information and "ProjectID" that links to fact_orders
Relationships:
- dim_org → dim_contracts: Connected by OrgCode.
- dim_contracts → fact_orders: Connected by ProjectId.
- dim_date → fact_orders: Connected by Date to the award date in fact_orders.
Pivot Table Setup (Tabular):
- Rows: Organizational Levels (e.g., Sector, Division).
- Columns: Months (Jan - Dec; fiscal year is July - June).
- Values: Active Order Count.
Issues I’m Facing:
- Date Relationship Issue: When the dim_date relationship is in place, the pivot table only counts orders with award dates in the corresponding month, regardless of the year. I need to count active orders for each month, not just those awarded during that month. For example: My pivot shows 15 orders for February, but when I drill down (double-click), I see 39 records for February. I’m not sure what’s being counted in the pivot.
- Removing the Date Relationship: When I remove the date relationship between dim_date and fact_orders
, the count comes closer to what I expect, but it’s still inaccurate. Worse, I can’t see what specific records are being counted.
Current Reporting Process:
Right now, our reports person manually pulls a new list of orders each month and adds it to a spreadsheet for a rolling 12-month dataset. This is inefficient and prevents us from seeing long-term or year-over-year trends, not to mention it creates huge files.
For example, to pull the September report, we filter by end date to exclude expired orders (i.e., count only orders with an end date after August 31).
What I’m Trying to Achieve:
I’m trying to create a measure in Power Pivot that automatically calculates active orders each month. The logic should count orders with:
- An award date before or during the current month.
- An end date after the last day of the previous month (e.g., for the September report, count all orders with an end date after August 31).
I feel like the current measures I’ve tried are filtering out orders that should still be active in previous months.
Measures I’ve Tried:
- First Measure: Attempts to count orders with an end date after the current month.
COUNT(fact_orders[PONumber]),
FILTER(
fact_orders,
fact_orders[PO Status] = "ordered" &&
fact_orders[POP End] > MAX(dim_date[Date])
)
)
- Second Measure: Attempts to count orders awarded before or during the current month and end after the previous month.
COUNT(fact_orders[PONumber]),
FILTER(
fact_orders,
fact_orders[PO Status] = "ordered" &&
fact_orders[POP Start] <= MAX(dim_date[Date]) && -- Includes all POs awarded on or before the current month
fact_orders[POP End] > EOMONTH(MAX(dim_date[Date]), -1) -- Excludes orders that ended in the previous month
)
)
Main Problem:
The measures above are not working as expected. In particular, it seems like orders that should still be active in previous months are being filtered out. I need a way to accurately reflect the month-over-month trend of active orders without having to maintain a rolling 12-month dataset manually.
Any ideas on how to solve this?
Thanks in advance!
April 15, 2021
Offline
For additional context of the Month-Over-Month Logic:
- January: 250 active orders (200 carried over from previous periods + 50 new Jan POs).
- February: 300 active orders (200 from Jan + 50 new Jan POs + 50 new Feb POs).
- March: 350 active orders (200 from Jan + 50 new Jan POs + 50 new Feb POs + 50 new Mar POs).
- April: 400 active orders (200 from Jan + 50 new Jan POs + 50 new Feb POs + 50 new Mar POs + 50 new Apr POs).
- May: 450 active orders (200 from Jan + 50 new Jan POs + 50 new Feb POs + 50 new Mar POs + 50 new Apr POs + 50 new May POs).
- June: 500 active orders (200 from Jan + 50 new Jan POs + 50 new Feb POs + 50 new Mar POs + 50 new Apr POs + 50 new May POs + 50 new Jun POs).
- July: 350 active orders (200 from Jan, Feb, May, Jun + 50 new Jul POs; Jan, Mar, and Apr orders have expired).
- August: 400 active orders (200 from Jan, Feb, May, Jun + 50 new Jul POs + 50 new Aug POs).
- September: 425 active orders (200 from Jan, Feb, May, Jun + 50 new Jul POs + 50 new Aug POs + 50 new Sep POs - 25 expired in Sep).
- October: 475 active orders (200 from Jan, Feb, May, Jun + 50 new Jul POs + 50 new Aug POs + 50 new Sep POs + 50 new Oct POs).
- November: 475 active orders (200 from Jan, Feb, May, Jun + 50 new Jul POs + 50 new Aug POs + 50 new Sep POs + 50 new Oct POs + 50 new Nov POs - 50 expired in Nov).
- December: 525 active orders (200 from Jan, Feb, May, Jun + 50 new Jul POs + 50 new Aug POs + 50 new Sep POs + 50 new Oct POs + 50 new Nov POs + 50 new Dec POs).
Explanation:
- January to June follows the previous logic, where 200 orders start active, and 50 new orders are added each month.
- July: Orders from January, March, and April expire, reducing the count to 350.
- September: 25 additional orders expire, reducing the total active count by 25.
- November: 50 more orders expire, reducing the total count by 50, but new orders still get added each month.
- The pattern continues as new orders are awarded and some previous orders expire, reflecting the dynamic changes in orders
April 15, 2021
Offline
I'm hoping this might add some clarity to my questions.
This is a sample of some orders. I'm trying to count orders in an "Ordered" PO status month over month based on when the award starts through till it ends.
For example: for P000057489, I would need it to start reporting in Aug of 2018 and be eached each month individually every month through Sept 2024 when it ends. So in Oct 2024 it would not longer be included in the monthly count. the same would apply for the other based on their dates.
as of right now, I'm having no luck getting this to work. I'm not getting correct count based on what i previously explained.
| PO Number | PO Status | Award Date | Last MOD Date | POP Start | POP End |
| P000057489 | Ordered | 08/29/2018 | 09/12/2024 | 08/29/2018 | 09/04/2024 |
| P000061548 | Ordered | 11/30/2018 | 09/12/2024 | 11/16/2018 | 09/04/2024 |
| P000061799 | Ordered | 11/19/2018 | 06/21/2024 | 11/26/2018 | 07/19/2024 |
| P000132484 | Ordered | 06/01/2022 | 06/13/2024 | 06/01/2022 | 07/20/2024 |
Any suggestions would be appreciated.
Thanks
Moderators
January 31, 2022
Offline
Perhaps you can upload a file with all of this. Quite a bit of text and formulas to read through and difficult to follow if one doesn't see it in context of the underlying data.
April 15, 2021
Offline
Thanks for replying Riny. Let me see about scrubbing my data, as it does have some sensitive company info, but maybe this will give some context if i focus solely on one order (P000057489) and how it should be counted month over month.
Right now, it's only being counted once for the month of August in 2018 (2019 is company fiscal year) as that's the month it was awarded, which i think is based on the active relationship between my dim_calendar table and the award date in my fact_orders table. When i make that relationship inactive, i get counts in difference months, but it's totally random and not just 1 even even filtered to just this one order.
This is what I'm trying to achieve. A measure that counts the PO as 1 every month starting with the Orders Start date (Aug 2019) and ending in the month of its End Date (Sept 2024).
| Fiscal Year | July | August | September | October | November | December | January | February | March | April | May | June |
| 2019 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |
| 2020 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2021 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2022 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2023 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2024 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2025 | 1 | 1 | 1 |
.
Moderators
January 31, 2022
Offline
I see what you mean but can't really get my head around how to do this with DAX measures in PP. Most likely I would do the ground work in Excel before loading the order dates table to the DM. But perhaps that's just because I can't think of an easier way.
Please have a look at the attached file. It's quite crude, but perhaps helpful anyway.
April 15, 2021
Offline
Riny, thanks so much. i think that gives me a good starting point to work towards solving my problem short term.
i'm thinking long term this wouldn't be a solution given we have thousands of orders and most carry anywhere from 12 to 5 years + periods of performance. That date table of true/false would just get out of control.
However, I totally see how this gets me in the direction i need to go.
Thanks
Moderators
January 31, 2022
Offline
Great! Perhaps one of the other frequent contributors here can help with a more solid (DAX) solution for you.
April 15, 2021
Offline
Update: I was able to get exactly the count i need month over month using this:
=CALCULATE(
COUNTROWS(fact_orders),
FILTER(
fact_orders,
fact_orders[Award Date] <= MAX(dim_calendar[Date]) &&
(ISBLANK(fact_orders[POP End]) || fact_orders[POP End] >= MIN(dim_calendar[Date]))
)
)
However, I had to make my active relationship between my dim_calendar (date) and fact_orders (award date) relationship inactive. I now have a message in my PivotTable fields saying "Relationship between table may be needed"
is there anyway to update this to ignore the "award date" relationship between the two table. The award date relationship is a key relationship to my overall dashboard report i'm working on.
Moderators
January 31, 2022
Offline
You seem to be quite advanced in DAX and it's difficult for me to add anything without seeing your file/model. However, you may want to look into the USERELATIONSHIP function. It allows you to activate an inactive relationship 'on-demand'.
1 Guest(s)
