Forum Discussion

CEllinger's avatar
CEllinger
Helper I
6 years ago
Solved

Redistributing a calculation across date range

I am attempting to quantify the redistribution of a backlog of sales orders (cause of some virus or something  😓) If anyone can help me I would be super appreciative.   I have a normal Date Table ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi CEllinger 

    Add three measures

    Measure = SUMX(ALLEXCEPT('date','date'[year]),[selected value])

Measure 2 =
CALCULATE (
    DISTINCTCOUNT ( 'date'[year monthno] ),
    FILTER (
        ALLSELECTED ( 'date' ),
        'date'[year]
            = MAX ( 'date'[year] )
            && 'date'[Date]
                > EOMONTH (
                    MAX ( 'disconnect date'[Date] ),
                    0
                )
    )
)


Measure 3 = [Measure]/[Measure 2]

Measure 4 =
IF (
    MAX ( 'date'[Date] )
        > EOMONTH (
            MAX ( 'disconnect date'[Date] ),
            0
        ),
    [rest  of the year] + [Measure 3]
)

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi CEllinger 

Is this expected?

If it is ok, please see my steps below:

Create a date table without relationship

Add [date] column to a slicer from this "disconnect date" table.

Create measures

selected value =
CALCULATE (
    SUM ( 'Table'[order] ),
    FILTER (
        'Table',
        'Table'[date]
            >= MIN ( 'disconnect date'[Date] )
            && 'Table'[date]
                <= MAX ( 'disconnect date'[Date] )
    )
)

all rest valuue =
CALCULATE (
    SUM ( 'Table'[order] ),
    FILTER (
        'Table',
        'Table'[date]
            < MIN ( 'disconnect date'[Date] )
            || 'Table'[date]
                > MAX ( 'disconnect date'[Date] )
    )
)
rest  of the year =
CALCULATE (
    SUM ( 'Table'[order] ),
    FILTER (
        'Table',
        'Table'[date]
            > MAX ( 'disconnect date'[Date] )
            && YEAR ( 'Table'[date] )
                = YEAR (
                    MAX ( 'disconnect date'[Date] )
                )
    )
)

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

CEllinger's avatar
CEllinger
Helper I
6 years ago

Thanks for replying v-juanli-msft !

 

I have been able to replicate that so far on my own. What I am trying to do is (from your model) take the 'All Selected Value" and spread it evenly across the "Rest of the Year." 

 

So rest of the year should be 

 

 

OrderSelectedAll RestRest of YearDesired Rest of Year

1

 

 1  
2 2  
3 3  
44   
55   
66   
77   
88   
99   
10 101010 +13
1 111+13
2 222+13

 

So the SUM(Selected) moved to the Date Range of (Rest of Year).

 

Is that more clear?

 

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi CEllinger 

    Add three measures

    Measure = SUMX(ALLEXCEPT('date','date'[year]),[selected value])

Measure 2 =
CALCULATE (
    DISTINCTCOUNT ( 'date'[year monthno] ),
    FILTER (
        ALLSELECTED ( 'date' ),
        'date'[year]
            = MAX ( 'date'[year] )
            && 'date'[Date]
                > EOMONTH (
                    MAX ( 'disconnect date'[Date] ),
                    0
                )
    )
)


Measure 3 = [Measure]/[Measure 2]

Measure 4 =
IF (
    MAX ( 'date'[Date] )
        > EOMONTH (
            MAX ( 'disconnect date'[Date] ),
            0
        ),
    [rest  of the year] + [Measure 3]
)

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • CEllinger's avatar
      CEllinger
      Helper I
      6 years ago

      v-juanli-msft  I could not have asked for better help!

       

      Thank you so much!