- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Help calculating purchasing requisitions backlog per month
Hi! I need help to calculate a monthly indicator related to open purchasing requisitions backlog for each month. Today, when a Purchasing requisition does not have a Purchase Order, we consider it in "Open" status, while when the PR has a PO, it has a "closed" status.
I want to calculate how many Open PRs I had at the last working day of each month. But the problem is that I cannot maintain the picture of the indicator for previous months. I can only calculate based on current picture.
Any recommendations? the data base is pretty basic:
PR number - PR creation date - PO number - Purchase Order creation date - Status (open - closed).
thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you @amitchandak
Hi, @tomasrcm
Based on your description, I have created a sample data as follows:
First, use WeekDay to create a new calculated column, and calculate that the pr date corresponds to the day of the week (1-7).
WeekNum = WEEKDAY('Table (2)'[PR creation date],1)
Is working day = IF('Table (2)'[WeekNum]<=6,"Yes","No")
Use the following DAX to calculate the number of prs on weekdays with status open:
Measure = COUNTAX(FILTER('Table (2)','Table (2)'[Is working day]="Yes"&&'Table (2)'[Status]="Open"),'Table (2)'[PR creation date])
Put the year and month in the table visual and put this measure in alongside:
I've provided the PBIX file for this use below.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thanks @v-jianpeng-msft! My main problem is that the requsitions will change status over time. For example, by end of May PR 1003 could be open and will be shown in the indicator as open, but if during June is closed I will not be able to know how many open PRs I had by end of May because the indicator will change.
I will need like a "snapshot" of end of may to compare it with the end of june indicator
thanks for the response!
I have attached the complete table I am using in case it provides more clarity.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, @tomasrcm
Thank you very much for your reply. After you import the data into Power BI, you can display and process the imported data, but Power BI does not have a data snapshot function like a database, it is a report display software. I think the best way to do this is to save your data for different months as different files and then import it into Power BI and then go through the previous DAX expression, calculate the difference so that you can understand how each month compares.
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@tomasrcm , if there are not dates like start and end. Then you need to create snapshots
DAX append approach should help
https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thanks @amitchandak! My main problem is that the requsitions will change status over time.
I will need like a "snapshot" of end of may to compare it with the end of june indicator. thanks for the suggestion!
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
09-24-2024 11:23 AM | |||
07-23-2024 11:55 PM | |||
Anonymous
| 04-17-2023 04:24 AM | ||
08-19-2024 10:39 AM | |||
09-05-2024 11:39 PM |
User | Count |
---|---|
119 | |
76 | |
57 | |
56 | |
44 |
User | Count |
---|---|
183 | |
120 | |
80 | |
65 | |
57 |