Hi all,
I need some help with making dynamic buckets based on my selected period and transactiondate. I am calculating my OHW based on my transaction date. I also have some buckets for 30 days 90 days etc. But what I would like to do is to calculate the OHW based on my selected period and transactiondate.
What I would like to see is:
What was the amount OHW on that selected Period in the bucket <30 days. 30-60 and so on.
I tried the following code:
DynamicBucketingMeasure =
VAR MinDate =
CALCULATE(
MIN(DIM_Calendar[Date]),
ALL(DIM_Calendar)
)
VAR MaxDate =
MAX(DIM_Calendar[Date])
Var SelectedPeriod =
CONCATENATE(
LEFT(
SELECTEDVALUE(DIM_Calendar[Year-Month]),4),
RIGHT(
SELECTEDVALUE(DIM_Calendar[Year-Month]),2)
)
var Selectedperiodnumber =
VALUE(SelectedPeriod)
VAR Summary =
SUMMARIZE(
FILTER(
OHW,
OHW[TransactionDate] <= MaxDate && OHW[TransactionDate] >= MinDate
&& OHW[Period] <= Selectedperiodnumber),
OHW[Period],
OHW[Test],
OHW[TransactionDate],
OHW[Amount],
"Bedrag", SUMX(OHW,OHW[Amount]),
"AantalDagenSelected", SUMX(OHW, DATEDIFF(OHW[TransactionDate], MaxDate, DAY))
)
VAR Bucketed =
ADDCOLUMNS (
Summary,
"Bucket", SWITCH (
TRUE (),
[AantalDagenSelected] > 0 && [AantalDagenSelected] < 30 , "< 30",
[AantalDagenSelected] >=31 && [AantalDagenSelected] < 60 , "30 - 60",
[AantalDagenSelected] >=61 && [AantalDagenSelected] < 90 , "60 - 90",
[AantalDagenSelected] >=91 && [AantalDagenSelected] < 180 , "90 - 180",
[AantalDagenSelected] >180, "> 180"
)
)
VAR Result =
SUMX ( Bucketed, IF ( [Bucket] = SELECTEDVALUE ( Buckets[Bucket] ), [Bedrag],0))
Return
Result
I have a test PBIX-file you can find here: Test DynamicBucket.pbix
As well as my expected outcomes.
Hope someone could help me out.
