Sum Values by condition for the previous months

[Anonymous]

Hi all

I am trying to replicate this logic in Power BI. I have a column 'Fees' where I would like to sum the values in it when the condition 'Banked' is Yes (Y). But when the condition is met I want it to sum 'Fees' for the previous months each time the condition is met. 

In the Excel above you will see Cell E4 = SUM(C2:C4) for Fund 1. So basically at each occurrence of Banked = "Y" then SUM the previous months values.

Is there a way to do this in either DAX or Power Query? I tried this but it obviously brings only the value back for that month where 'Banked' is Yes e.g. CALCULATE(SUM(Table1[FEES]), FILTER(Table1, Table1[BANKED] = "Y")) brings the FEES value for that Month only.

Appreciate anyone that can assist.

Regards

[Ashish_Mathur]

Hi,

Is it fair to say that everytime there is a Y in the banked column, we should sum the figures in the fees column of the 3 months ended the date of the current row

[Anonymous]

Hi @Ashish_Mathur Yes that's correct but it can be for any amount of months. 

@v-kelly-msft  I tried a variation of this before you posted also but get the following result: 

As you can see I get the wrong total - the last value should be 3 595 699 and not 1 453 554.

[Anonymous · Accepted solution]

Here's a much simpler measure that works in all circumstances:

Measure = 
var __tableToIterateOver =
    filter(
        'Table',
        'Table'[BANKED] = "Y"
    )
var __amount = 
    SUMX(
        __tableToIterateOver,
        var __currentDate = 'Table'[Date]
        var __result =
            calculate(
            
                var __prevDateOfBankedStatus =
                    CALCULATE(
                        MAX( 'Table'[Date] ),
                        'Table'[BANKED] = "Y",
                        'Table'[Date] < __currentDate
                    )				
                var __output =
                    CALCULATE(
                        SUM( 'Table'[FEES] ),
                        __prevDateOfBankedStatus < 'Table'[Date],
                        'Table'[Date] <= __currentDate
                    )
                return
                    __output,

                // Leave only the filter on Fund.
                ALLEXCEPT( 'Table', 'Table'[Fund] )
            )
        return
            __result
    )
return
    __amount

Best

D

[Anonymous]

Hi @Anonymous  thank you very much for posting your measure, however don't get the correct figures coming through as you see below:

your measure brings the total which is great but it is not summing the fees as to what I get from the other measure posted by @v-kelly-msft . I am looking at it closer to figure it out as well

[Anonymous]

Ok this is my fault @Anonymous  I missed an "=" sign here: 

[Anonymous]

Looks like my previous post didn't go through @Anonymous  - 

I missed the "=" sign in your posted measure:

Your measure does indeed give me the correct figures:

Thank you for all your help! Much appreciated 

Fareed

[Anonymous]

Now you can immediately see how efficient it is to write CLEAR AND EASY TO UNDERSTAND measures. You would be able to identify any mistakes in no time and also make changes to the logic quite easily.

Compare this to the first measure you were served...

Best
D

[Anonymous]

@Anonymous  thanks I appreciate your input. I have been implementing similar concepts in writing my code.

@v-kelly-msft  also appreciate your input, thanks again.

Cheers,

Fareed

[v-kelly-msft]

Hi @Anonymous ,

1. Go to query editor > add column> Index column;   (The column will be used for calculation)

2. Then create a measure as below:

Measure =
VAR a =
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Date] <= MAX ( 'Table'[Date] )
            && 'Table'[FUND] = SELECTEDVALUE ( 'Table'[FUND] )
    )
VAR b =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[BANKED] = "Y"
                && 'Table'[FUND] = MAX ( 'Table'[FUND] )
        )
    )
VAR _mindate =
    CALCULATE (
        MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index] < MAX ( 'Table'[Index] )
                && 'Table'[BANKED] = "Y"
                && 'Table'[FUND] = SELECTEDVALUE ( 'Table'[FUND] )
        )
    )
VAR _multisum =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= MAX ( 'Table'[Date] )
                && 'Table'[Date] > _mindate
                && 'Table'[FUND] = SELECTEDVALUE ( 'Table'[FUND] )
                && 'Table'[BANKED] = "N"
        ),
        'Table'[FEES]
    )
RETURN
    IF (
        MAX ( 'Table'[BANKED] ) = "Y",
        IF (
            b = 1,
            SUMX ( a, 'Table'[FEES] ),
            IF ( b > 1, _multisum + MAX ( 'Table'[FEES] ), BLANK () )
        )
    )

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft  I had a go at this and it all works, thank you. But I don't get the Total showing, it only tallies up at the row context.

I did some digging and know there are issues sometimes with Measure Totals, and I found this link https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/td-p/547907/

It helped to an extent but I still don't get the correct Total, as you can see:

Any ideas? The total should be  21,733,910 

[v-kelly-msft]

Hi  @Anonymous ,

Add a measure as below:

_total = SUMX(FILTER('Table','Table'[Measure]<>BLANK()),'Table'[Measure])

And you will see:

I have modified the .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

The code above is overly complex and it DOES NOT have to be. It can be DEAD simple.

Wait a bit and I'll show you.

Best
D

[Anonymous]

@v-kelly-msft  Wow, that's incredible. Thank you so much.

I would not have thought it would this complex to achieve in Power BI. I'm going to dissect this to fully understand what is happening.

One thing I noticed is that the Total for the Measure does not appear at the bottom. Is there a reason that happens?

Thank you again!