Forum Discussion

wes-shen-poal's avatar
wes-shen-poal
Icon for Helper III rankHelper III
9 years ago
Solved

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 4...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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's avatar
    Phil_Seamark
    9 years ago

    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
)
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
wes-shen-poal
Icon for Helper III rankHelper III
9 years ago

Thank you soooo much!!! Phil_Seamark

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

Really appreciate your help.