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

 

 

 

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.

4 Replies

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