Redistributing a calculation across date range

[CEllinger]

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 connected to a normal Shipped Sales Table. (Shipped dates for sales orders)

I used (a direct copy paste) @tondeli 's DAX Date Facts table and Dynamic Date Table to calculate the total number of backlogged orders we accrue for a given date range. (The separate tables allow my users to see their graphs change as they adjust the date slicer)

I want to know how to add the total backlogged orders to the dates that constitute the rest of the year.

Here is a screenshot for clarity.

I am trying to redistribute the Total Backlog over the rest of the year. Any advice from anyone out there?

(I have already tried just adding the Order Backlog to the Adding Test columns. The order backlog retains its Date Context if that is any help.)

[v-juanli-msft]

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]

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 

Order	Selected	All Rest	Rest of Year	Desired Rest of Year
1		1
2		2
3		3
4	4
5	5
6	6
7	7
8	8
9	9
10		10	10	10 +13
1		1	1	1+13
2		2	2	2+13

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

Is that more clear?

[v-juanli-msft · Accepted solution]

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]

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

Thank you so much!