Dynamic Buckets for days outstanding

Question

Hi There I need to calculate my WIP (OHW) for a specific selection so based on my selected period/date. I have a small table with my buckets.&nbsp;I Tried the following measure:DynamicBucketingMeasure =VAR MinDate =&nbsp; &nbsp; CALCULATE(MIN(DIM_Calendar[Date]), ALL(DIM_Calendar))VAR MaxDate =&nbsp; &nbsp; MAX(DIM_Calendar[Date])VAR SelectedPeriod =&nbsp; &nbsp; CONCATENATE(&nbsp; &nbsp; &nbsp; &nbsp; LEFT(SELECTEDVALUE(DIM_Calendar[Year-Month]), 4),&nbsp; &nbsp; &nbsp; &nbsp; RIGHT(SELECTEDVALUE(DIM_Calendar[Year-Month]), 2)&nbsp; &nbsp; )VAR Selectedperiodnumber =&nbsp; &nbsp; VALUE(SelectedPeriod)VAR Summary =&nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE(FACT_OHW,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[#Amount],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[#Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[#Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Period],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[TransactionDate],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FACT_OHW[Column],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Bedrag", SUM(FACT_OHW[#Amount_Tobill_Init_WIP]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "AantalDagenSelected", SUMX(FACT_OHW, DATEDIFF([TransactionDate], MAX(DIM_Calendar[Date]), DAY))&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VAR Bucketed =&nbsp; &nbsp; &nbsp; &nbsp; ADDCOLUMNS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Summary,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Bucket", SWITCH (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TRUE (),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [AantalDagenSelected] &gt;=0 &amp;&amp; [AantalDagenSelected] &lt; 30 , "&lt; 30",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [AantalDagenSelected] &gt;=31 &amp;&amp; [AantalDagenSelected] &lt; 60 , "30 - 60",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [AantalDagenSelected] &gt;=61 &amp;&amp; [AantalDagenSelected] &lt; 90 , "60 - 90",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [AantalDagenSelected] &gt;=91 &amp;&amp; [AantalDagenSelected] &lt; 180 , "90 - 180",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [AantalDagenSelected] &gt;180, "&gt; 180"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; RETURN&nbsp; &nbsp;SUMX ( Bucketed, IF ( [Bucket] = SELECTEDVALUE ( Buckets[Bucket] ), [Bedrag], 0 ) )When I place this measure in my table with the buckets I got the amount for my selectedperiod in &lt;30 bucket. And the other buckets are empty. The problem is, I only get the days from te selected month. So I need to add 2 more filters or variables.&nbsp;What I need to see is based on my Min and Max date the amount of OHW for a specific period. Something like this needs to be added to the measure:&nbsp;CALCULATE&nbsp;(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;SUM&nbsp;(&nbsp;FACT_OHW[#Amount])&nbsp; &nbsp; &nbsp; &nbsp; ,DATESBETWEEN&nbsp;(&nbsp;DIM_Calendar[Date],&nbsp;MinDate,&nbsp;MaxDate&nbsp;)&nbsp; &nbsp; &nbsp; &nbsp; ,&nbsp;FACT_OHW[Period]&nbsp;&lt;=&nbsp;Selectedperiodnumber&nbsp;)So in my Return I do need to get all the data based on Min and max date and not only the period I select. The Period also needs to be &lt;= SelectedPeriodNumber.Where do I need to add this?Who could help me out.

tk12345 · Accepted Answer

Found the solution myself today. Thanks for reaching out.DynamicBucketingMeasure =VAR MinDate =&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; MIN(DIM_Calendar[Date]),&nbsp; &nbsp; &nbsp; &nbsp; ALL(DIM_Calendar)&nbsp; &nbsp; )VAR MaxDate =&nbsp; &nbsp; MAX(OHW[Transaction_Date])Var SelectedPeriod =&nbsp; &nbsp; CONCATENATE(&nbsp; &nbsp; &nbsp; &nbsp; LEFT(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECTEDVALUE(DIM_Calendar[Year-Month]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; RIGHT(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECTEDVALUE(DIM_Calendar[Year-Month]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )var Selectedperiodnumber =&nbsp; &nbsp; VALUE(SelectedPeriod)VAR Summary =&nbsp; &nbsp; SUMMARIZE( &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL(OHW),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OHW[Period] = Selectedperiodnumber&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; OHW[TransactionDate] &lt;= MaxDate&nbsp; &nbsp; &nbsp; &nbsp; ), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;OHW[Key_OHW],&nbsp; &nbsp; &nbsp; &nbsp; "Bedrag", SUMX(OHW, OHW[#Amount]),&nbsp; &nbsp; &nbsp; &nbsp; "AantalDagenSelected", SUMX(OHW, DATEDIFF(OHW[TransactionDate], MaxDate, DAY))&nbsp; &nbsp; ) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;VAR Bucketed =&nbsp; &nbsp; &nbsp; &nbsp; ADDCOLUMNS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Summary,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Bucket", SWITCH (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TRUE (),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [AantalDagenSelected] &gt;= 0 &amp;&amp; [AantalDagenSelected] &lt; 30 , "&lt; 30",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [AantalDagenSelected] &gt;=30 &amp;&amp; [AantalDagenSelected] &lt; 60 , "30 - 60",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [AantalDagenSelected] &gt;=60 &amp;&amp; [AantalDagenSelected] &lt; 90 , "60 - 90",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [AantalDagenSelected] &gt;=90 &amp;&amp; [AantalDagenSelected] &lt; 180 , "90 - 180",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [AantalDagenSelected] &gt;=180, "&gt; 180"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )VAR Result =&nbsp; &nbsp; IF (&nbsp; &nbsp; &nbsp; &nbsp; ISFILTERED ( Buckets[Bucket] ),&nbsp; &nbsp; &nbsp; &nbsp; SUMX (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Bucketed,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF ( [Bucket] = SELECTEDVALUE ( Buckets[Bucket] ), [Bedrag], 0 )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; SUMX ( Bucketed, [Bedrag] )&nbsp; &nbsp; )RETURN&nbsp; &nbsp; Result

Forum Discussion

Dynamic Buckets for days outstanding

2 Replies

Related content

Dynamic Buckets based on slicer

Dynamic Buckets

Power BI Modeling Question – Dynamic Common Sample + Buckets + Weighted KPIs

Dynamic Top N Ranking with Bucket-Based Filter Context

Calculate DSO (Days Sales Outstanding) dynamically