cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper III

## Allocate Daily Target to Calendar Dates

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?

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.

2 ACCEPTED SOLUTIONS
Employee

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

Proud to be a Datanaut!

Employee

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

Proud to be a Datanaut!

6 REPLIES 6
Employee

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

Proud to be a Datanaut!

Helper III

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

Employee

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

Proud to be a Datanaut!

Helper III

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

Employee

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

Proud to be a Datanaut!

Helper III

Thank you soooo much!!! @Phil_Seamark

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors