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.

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!