Get amounts based on period buckets (based on reference date)

[joep78]

Hi all,

I'm looking for a solution that counts the Amounts per period buckets (for example 0 - 3 months, 4 - 6 months) based on a reference data (filter options):

data example:

period	amount
Jan-20	1250
Feb-20	1250
Mar-20	1250
Apr-20	1300
May-20	1300
Jun-20	1300
Jul-20	1350
Aug-20	1350
Sep-20	1350
Oct-20	1350
Nov-20	1350
Dec-20	1500
Jan-21	1500
Feb-21	1500
Mar-21	1500
Apr-21	1500
May-21	1500
Jun-21	1500
Jul-21	1500
Aug-21	1500
Sep-21	1600
Oct-21	1600
Nov-21	1600
Dec-21	1600

examples:

reference date Jan-20 

bucket 0 - 3 months = 3750 (Jan + Feb + Mar)

bucket 4 - 6 months  = 3900(Apr/May/Jun)

reference date Feb-20:

bucket 0 - 3 months = 3800(Feb + Mar + Apr)

bucket 4 - 6 months  = 3950(May/Jun/Jul)

So the calculation first needs to check the reference data before doing the calculation. 

I think I can add a date column that acts like the reference date and link this to the period, but not sure how to deal with this buckets.

hopefully sombody can help me out with this. Thanks in advance for a response!

Regards,

Joep

[v-janeyg-msft]

Hi, @joep78 

It’s my pleasure to answer for you.

According to your description, I think you can create two measure to meet your requirement.

You need to create an index column first.

Like this:

bucket 0 - 3 months =
VAR a =
    MAXX (
        FILTER ( ALL ( Table1 ), [period] = SELECTEDVALUE ( Table1[period] ) ),
        [Index]
    )
RETURN
    IF (
        ISFILTERED ( Table1[period] ),
        SUMX ( FILTER ( ALL ( Table1 ), [Index] IN { a, a + 1, a + 2 } ), [amount] ),
        0
    )

bucket 4 -6 months =
VAR a =
    MAXX (
        FILTER ( ALL ( Table1 ), [period] = SELECTEDVALUE ( Table1[period] ) ),
        [Index]
    )
VAR b =
    SUMX ( FILTER ( ALL ( Table1 ), [Index] IN { a + 3, a + 4, a + 5 } ), [amount] )
RETURN
    IF ( ISFILTERED ( Table1[period] ), IF ( ISBLANK ( b ), 0, b ), 0 )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[joep78]

Hi Janey,

Thanks for your repsonse. I'm close to the final solution but now it's not possible to filter on specific values from other (linked) tables. 

For example, I want to filter only on selected properties but when I add the property as a slicer, nothing happens (given the DAX I assume this is logical). Can you let me know what to do with this. I've got a demo file ready that I will share with you, hopefully this makes sense and you have a solution for this.

Regards,

Joep

[v-janeyg-msft]

Hi, @joep78 

Try to change 'all' to 'allselected' in the above formula.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[joep78]

Hi Janey,

I did change it in allselected and for the 0 -3 months buckets it works, but not for the 4-5 months buckets. Code now is:

bucket 0 - 3 months =

VAR a =

MAXX (

FILTER ( ALLSELECTED ( Table1 ), [period] = SELECTEDVALUE ( Table1[period] ) ),

[Index]

)

RETURN

IF (

ISFILTERED ( Table1[period] ),

SUMX ( FILTER ( ALL ( Table1 ), [Index] IN { a, a + 1, a + 2 } ), [amount] ),

0

)

bucket 4 -6 months =

VAR a =

MAXX (

FILTER ( ALLSELECTED ( Table1 ), [period] = SELECTEDVALUE ( Table1[period] ) ),

[Index]

)

VAR b =

SUMX ( FILTER ( ALLSELECTED ( Table1 ), [Index] IN { a + 3, a + 4, a + 5 } ), [amount] )

RETURN

IF ( ISFILTERED ( Table1[period] ), IF ( ISBLANK ( b ), 0, b ), 0 )

Filtering on property makes that bucket 4-6 months only gives 0 as a result.

Thanks for your reply.

[v-janeyg-msft]

Hi, @joep78 

Try:

'All' after 'return' doesn't need to be changed to 'allselected'.

Best Regards

Janey Guo

[joep78]

I  Thought I already tested that but clearly not 😬!

It look like it's working now, will test this on the real DB and as soon as it's working there I will accept it as a solution!

regards,

Joep

[v-janeyg-msft · Accepted solution]

Hi, @joep78 

Try like this:

bucket 0 - 3 months = 
VAR a =
    MAXX (
       ALLSELECTED( Table1 ),
        [Index]
    )
    var b= MAXX (
       ALLSELECTED( Table1 ),
        [PropertyCode]
    )
RETURN
    IF (
        ISFILTERED ( Table1[period] )&&ISFILTERED(Table2[Name]),
        SUMX ( FILTER ( ALL ( Table1 ),[PropertyCode]=b && [Index] IN { a, a + 1, a + 2 } ), [amount] ),
        0
    )

bucket 4 -6 months = 
VAR a =
    MAXX (
        ALLSELECTED ( Table1 ),
        [Index]
    )
 var c= MAXX (
       ALLSELECTED( Table1 ),
        [PropertyCode]
    )
VAR b =
    SUMX ( FILTER ( ALL ( Table1 ), [PropertyCode]=c&&[Index] IN { a + 3, a + 4, a + 5 } ), [amount] ) 

RETURN
    IF ( ISFILTERED ( Table1[period] )&&ISFILTERED(Table2[Name]), IF ( ISBLANK ( b ), 0, b ), 0 )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@joep78 , You need to create a measure like this

months from selected date(assume you date)

datediff(selecteddate(date[date]),max(date[date]),month)

or

datediff(selecteddate(date[date]),max(Table[date]),month)

then you need to create an independent table and bucket using that.

Refer my video for that - https://www.youtube.com/watch?v=CuczXPj0N-k