Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
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.
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:
Hi, @joep78
Try:
'All' after 'return' doesn't need to be changed to 'allselected'.
Best Regards
Janey Guo
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
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |