Need Help with Dynamic Workload Calculation Based on Planned Order and Launched Count in Power BI

[monak1]

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]

Hi,

Share the download link of the PBI file.

[monak1 · Accepted solution]

@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 

[Ashish_Mathur]

I cannot take care of the second requirement there.  Someone else will help you.

[amitchandak]

@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=42

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

[monak1]

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