Forum Discussion
Need Help with Dynamic Workload Calculation Based on Planned Order and Launched Count in Power BI
Hi Power BI Community, I am facing a challenge while calculating the current workload for each month in Power BI. My dataset includes columns for "Planned Order" and I have already calculated a measure called "Launched Count" . Furhter my table is linked to calendar based on creating date column in my main table. I need to calculate the current 'workload' by subtracting the "Launched Count" for the current month and then use that result as a base. Then, I need to add the "Planned Order" count for the next month and again subtract the "Launched Count" from the next month's number. This process should continue for each subsequent month. Here's a simplified example of my data and the desired result in Excel:
Please note that my table has directquery so can only create measures
I would like the the same result as in 'Workload' column in below table.
| Planned Order | Launched Count | Workload | |
| May | 20 | 5 | 15 |
| June | 30 | 2 | 43 |
| July | 40 | 20 | 63 |
Could you please help me create a DAX measure that achieves this calculation in Power BI? I tried using the "Workload Running" measure, but it didn't produce the expected result.
Any guidance or assistance would be greatly appreciated
Ashish_Mathur dax amitchandak PhilipTreacy tamerj1 Greg_Deckler
Ashish_Mathur sir, thanks for your reply. here is the dummy file I created . please have a look
https://drive.google.com/file/d/11myAEB3DNPQpWmOqtWt33c6yCgkWvjjV/view?usp=drive_link
I am providing some additional information
- I need to substract Launched number from Total Planned Opend Order to get the 'Workload' for the current month and the obtained value will carry forward to next month and will be added to 'Planned Opened Order of that month and will again exclude launched number of that month to get 'workload'
- In case any value turns negative for 'Workload', then we need to consider it 0 and it will again carry forward .
- We can use calculated column if needed as I have now changed the data from Directquery to Import.
I am really struggling with this so I will really be grateful to you if you can provide me the solution if it exists at all ?
Thanks a lot
5 Replies
monak1 , if you have date, or create date date using month, With help from date table
Choose one of the 4
Cumm = CALCULATE(SUM(Table[Planned Order]) - SUM(Table[Lunched Count]),filter(all('Date'),'Date'[date] <=max('Date'[date])))
Cumm = CALCULATE(SUM(Table[Planned Order]) - SUM(Table[Lunched Count]),filter(allselected(date),date[date] <=max(date[Date])))
Cumm on Date = CALCULATE(SUM(Table[Planned Order]) - SUM(Table[Lunched Count]), Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))
Cumm on Date = CALCULATE(SUM(Table[Planned Order]) - SUM(Table[Lunched Count]), Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
ok thanks a lot @amitchandak . However just wanted to tell you that the planned order column in my table is a string so we can't use SUM. isn't it ? moreover, the 'Launched' is a measure and not column. Could you please tell me how to implement it correctly. I have been quite struggling with this . thanks in advance
Hi,
Share the download link of the PBI file.
Ashish_Mathur sir, thanks for your reply. here is the dummy file I created . please have a look
https://drive.google.com/file/d/11myAEB3DNPQpWmOqtWt33c6yCgkWvjjV/view?usp=drive_link
I am providing some additional information
- I need to substract Launched number from Total Planned Opend Order to get the 'Workload' for the current month and the obtained value will carry forward to next month and will be added to 'Planned Opened Order of that month and will again exclude launched number of that month to get 'workload'
- In case any value turns negative for 'Workload', then we need to consider it 0 and it will again carry forward .
- We can use calculated column if needed as I have now changed the data from Directquery to Import.
I am really struggling with this so I will really be grateful to you if you can provide me the solution if it exists at all ?
Thanks a lot
I cannot take care of the second requirement there. Someone else will help you.
