Allocate Daily Target to Calendar Dates

[wes-shen-poal]

Hi there,

I have a data table called "VMS VesselMovementVehicle"

This data table consists of the following information

What the data is saying is that e.g. Row 2: CDG car company has 478 cars being shipped on Ship Voyage ID123. On 02/07/2017 (July 2nd), at 20:08 pm, all 478 cars have arrived onto its destination port and has been stored. The CDG car company has 4.41 days from 02/07/2017 20:08pm to collect these cars from the port to vacate more car spaces at the port. So the average daily target amount of cars to be collect by CDG is 108.

My end goal is to provide a table visualisation as per below:

So based on the number of CDG cars being shipped across differect voyages, I can calculate a total daily target amount of cars to collect.

E.g. for 06/07/2017, CDG should collect 46 cars from Voyage ID123, 198 cars from ID456, 101 cars from ID789. Which comes to a total target of 345 cars to be collected for this day.

Can someone kindly guide me through the calculated columns/formulas I should do to achieve this table visualisation?

Thank you in advance.

Wes

PS. If it is easier to allocate the same amount of daily target across the dates for each voyage that is ok too.

Please kindly specify which approace your solution is referring to. Thanks heaps.

[Phil_Seamark · Accepted solution]

Hi @wes-shen-poal

Does this help?  Either that or convert the [Date Stored] col to a DATE rather than a DATETIME

Expanded Table = FILTER(
            CROSSJOIN(CALENDARAUTO(),'Table1') ,
            [Date] >= 'Table1'[Date Stored] -1
            && [Date] <= 'Table1'[Date Stored] + ROUNDUP('Table1'[Days to Vacate],0)
            )

[Phil_Seamark]

Hi @wes-shen-poal

I think I am close with the following calculated table 

Expanded Table = FILTER(
            CROSSJOIN(CALENDARAUTO(),'Table1') ,
            [Date] >= 'Table1'[Date Stored]
            && [Date] <= 'Table1'[Date Stored] + ROUNDUP('Table1'[Days to Vacate],0)
            )

And then I added the following calculated column to the above table

End Goal = 
VAR DailyTarget = 'Expanded Table'[Daily Target] 
VAR Accum = 'Expanded Table'[Count of Cars] - CALCULATE(
            SUM('Expanded Table'[Daily Target]),
            FILTER(
                ALL('Expanded Table'),
                'Expanded Table'[Car Company] = EARLIER('Expanded Table'[Car Company])
                && 'Expanded Table'[Date] <= EARLIER('Expanded Table'[Date]) 
                && 'Expanded Table'[Ship Voyage] = EARLIER('Expanded Table'[Ship Voyage])
                )
               )
RETURN SWITCH(TRUE() ,
                Accum > DailyTarget , DailyTarget ,
                Accum < 0 , 0 ,
                Accum
)              

Which gave me this 

You can grab a copy of the PBIX file here :

https://1drv.ms/u/s!AtDlC2rep7a-kHJjamGJ1rJ-pbZT

[wes-shen-poal]

Thanks again for your help @Phil_Seamark

It seems like we're missing the number of cars that needs to be collected on the date of the "Date Stored" for each row of Ship Voyage

e.g. Voyage ID123 should also have 108 cars collected on 02/07/2017

Do you know how we can fix this?

Thanks,
Wes 

[Phil_Seamark · Accepted solution]

Hi @wes-shen-poal

Does this help?  Either that or convert the [Date Stored] col to a DATE rather than a DATETIME

Expanded Table = FILTER(
            CROSSJOIN(CALENDARAUTO(),'Table1') ,
            [Date] >= 'Table1'[Date Stored] -1
            && [Date] <= 'Table1'[Date Stored] + ROUNDUP('Table1'[Days to Vacate],0)
            )

[wes-shen-poal]

Hi @Phil_Seamark

Was wondering if you can assist further. Is there a way to add the Date column onto Table1 rather than creating a new table entirely? The reason I ask is because everytime I try creating the new "Expanded Table" table, it says my computer has ran out of memory. And also, my current table is related to other tables in my model which is what gives me the information I need to generate my end goal table.

Thanks

Wes

[Phil_Seamark · Accepted solution]

Oh and please change the calculated column to this

End Goal = 
VAR DailyTarget = 'Expanded Table'[Daily Target] 
VAR Accum = 'Expanded Table'[Count of Cars] - CALCULATE(
            SUM('Expanded Table'[Daily Target]),
            FILTER(
                ALL('Expanded Table'),
                'Expanded Table'[Car Company] = EARLIER('Expanded Table'[Car Company])
                && 'Expanded Table'[Date] < EARLIER('Expanded Table'[Date]) 
                && 'Expanded Table'[Ship Voyage] = EARLIER('Expanded Table'[Ship Voyage])
                )
               )
RETURN SWITCH(TRUE() ,
                Accum > DailyTarget , DailyTarget ,
                Accum < 0 , 0 ,
                Accum
)       

[wes-shen-poal]

Thank you soooo much!!! @Phil_Seamark

Now I'm going to try and understand your code to help me learn.

Really appreciate your help.