Dynamic Buckets for days outstanding

[TK12345]

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. 

I Tried the following measure:

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(FACT_OHW,

            FACT_OHW[#Amount],

            FACT_OHW[#Column],

            FACT_OHW[#Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Period],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[Column],

            FACT_OHW[TransactionDate],

            FACT_OHW[Column],

            "Bedrag", SUM(FACT_OHW[#Amount_Tobill_Init_WIP]),

            "AantalDagenSelected", SUMX(FACT_OHW, DATEDIFF([TransactionDate], MAX(DIM_Calendar[Date]), 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"

            )

        )

    RETURN

   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 <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. 

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: 

CALCULATE (

        SUM ( FACT_OHW[#Amount])

        ,DATESBETWEEN ( DIM_Calendar[Date], MinDate, MaxDate )

        , FACT_OHW[Period] <= Selectedperiodnumber )

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 <= SelectedPeriodNumber.


Where do I need to add this?

Who could help me out.

[TK12345 · Accepted solution]

Found the solution myself today. Thanks for reaching out.


DynamicBucketingMeasure =

VAR MinDate =

    CALCULATE(

        MIN(DIM_Calendar[Date]),

        ALL(DIM_Calendar)

    )

VAR MaxDate =

    MAX(OHW[Transaction_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(

            ALL(OHW),

            OHW[Period] = Selectedperiodnumber

            && OHW[TransactionDate] <= MaxDate

        ),              

       OHW[Key_OHW],

        "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] >=30 && [AantalDagenSelected] < 60 , "30 - 60",

                [AantalDagenSelected] >=60 && [AantalDagenSelected] < 90 , "60 - 90",

                [AantalDagenSelected] >=90 && [AantalDagenSelected] < 180 , "90 - 180",

                [AantalDagenSelected] >=180, "> 180"

            )

        )

VAR Result =

    IF (

        ISFILTERED ( Buckets[Bucket] ),

        SUMX (

            Bucketed,

            IF ( [Bucket] = SELECTEDVALUE ( Buckets[Bucket] ), [Bedrag], 0 )

        ),

        SUMX ( Bucketed, [Bedrag] )

    )

RETURN

    Result

[v-shex-msft]

Hi @TK12345,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng