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.
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.
Solved! Go to Solution.
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) )
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 )
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
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
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) )
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
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 )
Thank you soooo much!!! @Phil_Seamark
Now I'm going to try and understand your code to help me learn.
Really appreciate your help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |