How to calculate previous months based on current month measures

[username2]

Hi Community

I have a matrix visual and there's one metric I can't figure out how to calculate. It's Outstanding

Metric	2022 08	2022 09	2022 10
Open	105	110	115
Closed	100	105	110
Outstanding	190	195	200

Current month's Outstanding is easy, but for this particular report calculating previous months is the difficult part.

To calculate previous month's outstanding (2022 09) I need to add Outstanding + Open - Closed for 2022 10, for 2022 08 I need to add Outstanding + Open - Closed for 2022 09 and so on.

I can achieve this in Excel easily, I just can't translate it to PBI.

Any advise would be appreciated.

[username2]

I don't seem to be able to attach a file here.

The raw data look like this, I only included the columns that are used to calculate values in the table I included in my original message:

Data Set	Key	Status	Date
Open	110B  2523_2	Closed	2022/10
Closed	110D 80082_2	Open	2022/09
Closed	110G  2671_2	Open	2022/09
Closed	110G  4440_2	Open	2022/09
Closed	112C   103_2	Open	2022/09
Closed	112W   954_2	Open	2022/09
Closed	112W  1149_2	Open	2022/11
Closed	113J     2_2	Open	2022/11
Closed	113W   221_2	Open	2022/11
Open	114A 82006_2	Closed	2022/11
Open	114A 82010_2	Closed	2022/11
Open	114W    59_2	Closed	2022/11
Open	114W    82_2	Closed	2022/11
Open	114W    86_2	Closed	2022/11
Closed	114W    90_2	Closed	2022/11
Closed	115A 82017_2	Closed	2022/10
Closed	115G     8_2	Closed	2022/10
Closed	115W    28_2	Closed	2022/10
Closed	116A 84000_2	Closed	2022/10

 Just to repeat the data is summarised to a table looking like this: 

Metric	2022/09	2022/10	2022/11
Open	0	1	5
Closed	5	4	4
Outstanding	1	4	3

To calculate outstanding for 2022/11 I just count keys where Status is Open.

To calculate outstanding for 2022/10 I need to do this calculation: Outstanding 2022/11 + Open 2022/11 - Closed 2022/11

To calculate outstanding for 2022/09 I need to calculate: Outstanding 2022/10 + Open 2022/10 - Closed 2022/10

and so on. Hope this helps

[Ashish_Mathur]

Hi,

How does one determine the o/s of 2022/11.  Is that a hard coded figure of 3?

[username2]

 I can see now that the table didn't paste correctly:

Metric	2022/09	2022/10	2022/11
Open	0	1	5
Closed	5	4	4
Outstanding	6	9	8

outstanding for 2022/11 I just count keys where Status is Open which is 8

[Ashish_Mathur]

For 2022/11, the number of rows where status is Open is only 2.  How did you get 8????

[username2]

No, it's 8, as I said, for current month I only count rows with status open, regardless of date. Then for previous months outstanding calculation I do the calculations I already mentioned in my previous replies.

the date in the dataset is only used for calculating open and closed.

Data Set	Key	Status	Date
Open	110B  2523_2	Closed	2022/10
Closed	110D 80082_2	Open	2022/09
Closed	110G  2671_2	Open	2022/09
Closed	110G  4440_2	Open	2022/09
Closed	112C   103_2	Open	2022/09
Closed	112W   954_2	Open	2022/09
Closed	112W  1149_2	Open	2022/11
Closed	113J     2_2	Open	2022/11
Closed	113W   221_2	Open	2022/11
Open	114A 82006_2	Closed	2022/11
Open	114A 82010_2	Closed	2022/11
Open	114W    59_2	Closed	2022/11
Open	114W    82_2	Closed	2022/11
Open	114W    86_2	Closed	2022/11
Closed	114W    90_2	Closed	2022/11
Closed	115A 82017_2	Closed	2022/10
Closed	115G     8_2	Closed	2022/10
Closed	115W    28_2	Closed	2022/10
Closed	116A 84000_2	Closed	2022/10

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[ryan_mayu]

To calculate outstanding for 2022/11 I just count keys where Status is Open.

why the outstanding for 2022/11 is 3 ? how can we get that result?

[ryan_mayu]

@username2 

could you pls provide the sample data?