Account Overdrawn This Month - Show ALL in matrix

[bullius]

Hi

I have a list of bank balances showing a snapshot of each account's balance on a given date.

E.g.

Date	Account	Balance	OD This Month
01/08/2016	1	£10	FALSE
01/08/2016	2	-£5	FALSE
15/08/2016	2	£15	FALSE
01/09/2016	1	£20	FALSE
01/09/2016	2	-£30	TRUE
01/09/2016	3	£40	FALSE
15/09/2016	1	-£10	TRUE
15/09/2016	2	£45	FALSE
15/09/2016	3	£30	FALSE

I have added a calculated column to indicate whether an account has been overdrawn this month or not, using the following formula:

OD This Month =

    IF(
        AND(
            [Balance] < 0,
            MONTH( [Date] ) = MONTH( MAX( [Date] ) )
        ),
        TRUE(),
        FALSE()
    )

When put into a matrix, this looks like this (created in excel, but same in pbi):

The problem is, I want to see the balances on the days that the account wasn't overdrawn as well - where the gaps are (just for those accounts that have been overdrawn at some point during this month).

The way I can think to do it is to create a formula that returns TRUE next to all balances (including positive ones) from an account that has been overdrawn at some point this month.

Could anyone help with creating this formula or is there another solution?

Thanks!

[OwenAuger · Accepted solution]

Hi @bullius

Here is an example of how it could be done, just using a measure rather than any calculated columns:

1. Create a Calendar table with at least date and month columns
2. Relate your main table (I've called it Balance) to the Calendar table
3. Create the following measures:
   

   Account Balance = SUM ( Balance[Balance] )
   
   Account Balance for months where overdrawn = 
   CALCULATE (
       [Account Balance],
       FILTER (
           ADDCOLUMNS (
               SUMMARIZE ( Balance, Balance[Account], 'Calendar'[End of Month] ),
               "MinBalance", CALCULATE (
                   MIN ( Balance[Balance] ),
                   ALLEXCEPT ( 'Calendar', 'Calendar'[End of Month] )
               )
           ),
           [MinBalance] < 0
       )
   )

   or

4. Account Balance for months where overdrawn v2 = 
   SUMX (
       VALUES ( Balance[Account] ),
       IF (
           CALCULATE (
                MIN ( Balance[Balance] ),
                ALL ( 'Calendar' ),
                VALUES ( 'Calendar'[End of Month] )
           )
                < 0,
           [Account Balance]
       )
   )

Sample pbix file here:

https://www.dropbox.com/s/ku2cuioh5blyoh9/Account%20Overdrawn.pbix?dl=1

I'm sure there are other ways of implementing similar logic.

Owen 🙂

[bullius]

Thanks @OwenAuger

This logic works! I hadn't thought of approaching it like that.

[v-yuezhe-msft]

Hi @bullius,

I am not sure what specific values you want to show in your matrix visual, could you please describe more details?

If you want display Matrix visual as follows, you don’t need to use OD This Month slicer, just choose 1 and 2 using visual filter to filter your matrix.

Thanks,
Lydia Zhang

[bullius]

Thanks @v-yuezhe-msft

The above data is just an example. Your solution would work for this data, but the solution for the actual data needs to be more dynamic.