Advanced measure - sum of positive / negative WIP on project and department level

[Anonymous]

Dear all,

I'm struggling to get a measure to show the result in the way I need it to be showed. I can't share the underlying data source as it's confidential data but I'll do my best showing what I am struggling with.

Data:

Project	Department	Year	Amount
A	1	2019	100
A	1	2020	-500
A	2	2020	150
B	1	2019	300
B	2	2019	-700
B	2	2020	-650

I have created a measure called "WIP 2019" and "WIP 2020" (based on column "Amount"). The goal of this measure is to show the actual WIP per project per year.

My measure is the following: 

WIP 2020 = calculate(sum([Amount]), [Year]=2020).
For WIP 2019 I used the same logic applied to 2019.

So for Project A, WIP would be 100 in 2019 and -350 in 2020.

This measure is working fine.

However, as a next step, I need to calculate WIP+ and WIP- separately. On project level, all projects with a positive WIP should be identified under "WIP+". The years are also important as WIP+ and WIP- need to be calculated per year. For projects with a negative WIP, they should be counted under "WIP-".

I have tried out several ways to calculate "WIP+" and "WIP-" on project level per year, none of them giving the correct output for my entire dataset. So I'm looking for help to establish a good measure or calculated column to calculate "WIP+ 2019", "WIP- 2019", "WIP+ 2020" and "WIP- 2020". For example: Project A in my table above should be calcualted under "WIP+2019" for 2019 and under "WIP- 2020" for 2020.

My output needs to look like this:

Project	WIP+ 2019	WIP- 2019	WIP+ 2020	WIP- 2020
A	100	0	0	-350
B	0	-400	0	-650

On top of that, I would need this "WIP+ 2019", "WIP- 2019", "WIP+ 2020" and "WIP- 2020" to be summed up on department level too. So all projects with a positive WIP in 2019 need to be summed up to get the total WIP+ 2019 per department. My struggle here is to have a measure calculate positive vs negative WIP on project level and summing this up to department level.

For example, for department 1 of my table above, project A should be calculated in column "WIP+ 2019" for 2019 and project A should be calculated as "WIP- 2020" for 2020. For department 2, in 2020, some projects need to be calcualted under WIP+ and others under WIP-. See table below.

All measures that I have tried so far were calculating WIP+ or WIP- on department level rather than on project level and then to make the sum per department.

My output needs to look like this:

Department	WIP+ 2019	WIP- 2019	WIP+ 2020	WIP- 2020
1	400	0	0	-500
2	0	-700	150	-650

Is anyone able to help me establish what I need?

Thank you!

[v-yiruan-msft]

Hi @Anonymous ,

I created a sample pbix file for you, please check if that is what you want.

Best Regards

Rena

[Anonymous]

Dear @v-yiruan-msft ,
Thank you so much for your response! This has helped my view a lot already!

However, there's one more thing that needs to be improved as my data for 2019 is showing incorrectly.

I need the figures for 2019 to be limited to the same period as the figures available in 2020.
For example: I have figures till August 2020. I need my figures for 2019 to resemble the same period (January - August 2019) in that case. How can I make that happen?
Thank you!

[v-yiruan-msft]

Hi @Anonymous ,

Could you please provide some sample data(include periods) and your desired result with examples in order to give you a proper solution? Thank you.

Best Regards

Rena

[Anonymous]

Dear @v-yiruan-msft ,

Sure! I will add some information to my originally added data above. See table below.

Data:

Project	Department	Year	Transaction Date	Amount
A	1	2019	1 Mar 2019	100
A	1	2020	4 Apr 2020	-500
A	2	2020	15 Aug 2020	150
A	1	2019	23 Oct 2019	1000
B	1	2019	17 Feb 2019	300
B	2	2019	11 Sep 2019	-700
B	2	2020	30 Aug 2020	-650

So if my data is filtered till August 2020 for 2020 - I would need the figures in 2019 to reflect the same period (Jan - Aug 2019). That means that the transactions of September and October 2019 in the table should be left out of the table result.

I need to be able to do this on project level and department level separately. Also, WIP+ and WIP- need to be reported separately per year.

Example on department level:

Department	WIP+ 2019	WIP- 2019	WIP+ 2020	WIP- 2020
1	400			-500
2				-500

Example on project level: 

Project	WIP+ 2019	WIP- 2019	WIP+ 2020	WIP- 2020
A	100			-350
B	300			-650

Does this help?

Thank you!

[v-yiruan-msft]

Hi @Anonymous ,

Sorry for delay. I created another sample pbix based on the requirement in your latest post, you can get it from the attachment.

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Anonymous]

Dear @v-yiruan-msft ,

Thank you for your response! This works. However, there's one tweak. For projects that were present in 2019 and no longer in 2020 (I added project C in the screenshot below as an example), data doesn't appear in the final tables, while it should appear as WIP+ 2019 for an amount of 200. Can this be processed some way as well?

Thank you!

Best regards!

[v-yiruan-msft]

Hi @Anonymous ，

As checked the sample data in your last post, Project C is on Feb 3, 2019. So could you please set the start date in slicer as the previous date of that date? Then check whether the data in related matrix can be displayed correctly.

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Anonymous]

Dear @v-yiruan-msft ,

My Power BI works with European dates (DD/MM/YYYY) so the date was in the period selected in the slicer in my own report. I'll try to explain by the means of some screenshots of my own report what is happening. 

Below entries are for one project, let's say project X. So there's an entry of +5.848 EUR on 30 June 2019 and -5.848 EUR on 1 January 2019.

However, when I select period 1 January 2020 till 31 May 2020 in my slicer, this project doesn't show up while an amount of -5.848 EUR should show up as the entry of 30 June shouldn't be counted yet.

Slicer (from Dates table - European date format)):

On the other hand, projects that do have a date in 2020, like project Y below, are showing up correctly for the calculation of WIP 2019.

So I don't really understand what is going wrong. I have checked this for multiple examples, and all items showing wrong for WIP 2019 don't have any entry in 2020 such as in the example above.

I am using this formula:

WIP- 2019 per project =

var _mindate=MIN('Dates'[Date])

var _maxdate=MAX('Dates'[Date])

var mindateLyear=IF(DAY(_mindate-365)<>DAY(_mindate),_mindate-366, _mindate-365)

var maxdateLyear=IF(DAY(_maxdate-365)<>DAY(_maxdate),_maxdate-366, _mindate-365)

VAR _sumofAmount =

CALCULATE( SUM ('Rap+Transactions'[bedrag]),DATESBETWEEN('Dates'[Date],mindateLyear,maxdateLyear))

RETURN

IF ( _sumofAmount < 0, _sumofAmount, BLANK() )

Final formula to group correctly on service line level is:

WIP- 2019 = SUMX(GROUPBY('Rap+Transactions','Rap+Transactions'[Service Line],'Rap+Transactions'[Project] ), [WIP- 2019 per project])

I need this additional step as otherwise I don't get a any figures on service line level.

I had to choose "'Dates'[Date] in my calculate function because when I use my date from the transactions table, it doesn't calculate correctly at all (highlighted in red in the formula, this deviates from your formula because of this reason).

Any ideas what could be going wrong?

[Anonymous]

Dear @yingyinr ,

Was this information helpful? Are you able to help me?

Best regards,