Advanced Filtering for Cumulative values

[MrFlox]

Hi Everyone, 

I have a problem with a cumulative measure. Would appreciate if someone could help.

My columns are:

• "Account ID" - unique for every account and can be found through multiple dates in the data. My file contains multiple Account IDs.
• "Date" - my monthly dates (end of month always)
• "Status" - this is a status classification for a particular account at that particular Date. The status can change from one Date to the next(!).
• "Charge" - this is a charge per account on that particular Date.

My problem:

I try to create a "Cumulative Charge" bar chart with Date on the X Axis, and I would like this cumulative bar chart to be clustered by the Status of each account, kind of like the below:

You will notice that Y axis goes below zero. This should not be so, as no Account ID has a negative cumulative charge. What happens is that there are some negative charges for accounts with a  "POCI-PE" status, and that charge gets summed up separately in the cumulation formula. Below is an example for a single Account ID:

Date	ACCOUNT ID	Status	Charge	Cumulative Charge
9/1/2018	1324	POCI - NPE	0	0
9/30/2018	1324	POCI - NPE	415491.33	415491.33
10/31/2018	1324	POCI - PE	-11824.23	403667.1
11/30/2018	1324	POCI - PE	-4924.21	398742.89
12/31/2018	1324	POCI - NPE	-17930.03	380812.86
1/31/2019	1324	POCI - NPE	-4894.88	375917.98
2/28/2019	1324	POCI - NPE	-4915.58	371002.4
3/31/2019	1324	POCI - NPE	-369026.55	1975.85
4/30/2019	1324	POCI - NPE	-801.18	1174.67
5/31/2019	1324	POCI - NPE	-403.24	771.43
6/30/2019	1324	POCI - NPE	-771.43	0
7/31/2019	1324	POCI - NPE	0	0
8/31/2019	1324	POCI - NPE	0	0
9/30/2019	1324	POCI - NPE	0	0
10/31/2019	1324	POCI - PE	0	0
11/30/2019	1324	POCI - PE	0	0
12/31/2019	1324	POCI - PE	0	0
1/31/2020	1324	POCI - PE	0	0
2/29/2020	1324	POCI - PE	0	0
3/31/2020	1324	POCI - PE	194030.02	194030.02
4/30/2020	1324	POCI - PE	920.58	194950.6
5/31/2020	1324	POCI - PE	925.08	195875.68
6/30/2020	1324	POCI - PE	-195875.68	0
7/31/2020	1324	POCI - PE	0	0
8/31/2020	1324	POCI - PE	0	0
9/30/2020	1324	POCI - PE	0	0
10/31/2020	1324	POCI - PE	0	0
11/30/2020	1324	POCI - PE	0	0
12/31/2020	1324	POCI - PE	0	0
1/31/2021	1324	POCI - PE	0	0
2/28/2021	1324	POCI - PE	0	0
3/31/2021	1324	POCI - PE	0	0

You can see that for the account "1324", the cumulative charge at any date is never negative. But if the cumulation formula segregates the statuses, then for "POCI-PE" status there is a summation of negative values, and a negative cumulative value is created. This is why the above bar chart gave me negative values.

I thus need a cumulative charge measure function which sums the charges of that account up until the current Date irrespective of any Status, but that cumulative charge must belong to the current status of that account, and labeled as so in the bar chart.

E.g for the above account "1234":

• the cumulative charge as at 12/13/2018 would be 380812, and that number would belong to a "POCI-NPE" Status in the bar chart which is its Current status (i.e in the red color).
• the cumulative charge as at 10/31/2019 would be 0, and that number would belong to a "POCI-PE" Status in the bar chart which is its Current status (i.e in the yellow color). But now there would not be any negative cumulative values.

I believe there must be a solution hiding in the filtering of the formula. Was not able to find out though.

Many thanks for reading and I hope for a solution!:)

[MFelix]

Hi @MrFlox ,

The calculation that you refer for the 31/12/2018 is correct when you don't have split between the status when you look at the POCI-NPE the value is 397.561 and the -16.748 would be part of the "POCI-PE" .

If I understand correctly what you want is that the values present the cumulative in this case 380 but for the status to be presented has part of the higher status in this case the POC-NPE is this correct? 

So in this case we would calculate the 380K and then categorize it has POC-NPE since the total cumulative is lower than the POC-NPE status independently?

Can you please elaborate a little bit more.

[MrFlox]

Thank you Miguel for taking interest.

Actually what i want is simpler. I just want the Cumulative Charge not to split between statuses.
I want the cumulative charge to belong to the current status at that date. 

E.g For 31/05/2019 the cumulative charge will be 771.43. And this 771.43 should be labeled as POCI-NPE in my bar chart (for that date), because POCI-NPE is the status of 31/05/2019.

A month later, the cumulative charge will change and the status may change as well, so I want the next date's cumulative charge to be labeled with the next date's status.

But importantly, the cumulative charge should not filter between statuses. It should sum the charges of the whole Account ID irrespective of what statuses it has.

Please let me konw in case of any other questions.

[MFelix · Accepted solution]

Hi @MrFlox ,

I believe that you need to do a condittional formatting measure to make this happen. In this case I have created a calendar table and the following measures:

cumulative value =
CALCULATE (
    SUM ( 'Table'[Charge] ),
    FILTER ( ALL ( 'calendar' ), 'calendar'[Date] <= MAX ( 'calendar'[Date] ) )
)


Formattingbystatus =
SWITCH (
    SELECTCOLUMNS (
        TOPN (
            1,
            FILTER (
                ALL ( 'Table'[Status], 'Table'[Date] ),
                'Table'[Date] <= MAX ( 'calendar'[Date] )
            ),
            'Table'[Date], DESC
        ),
        "DDD", 'Table'[Status]
    ),
    "POCI - NPE", "Red",
    "POCI - PE", "Yellow"
)

Now if you use the condittional formatting you will get the following result:

You need to add on the second measure a color for each of the status you have in this example you only share two.

Check PBIX file attach.