shifting sales dates based on dates of events

[dniedrauer]

Table 1

Sales 

Date

Vessel IMO

[link based on vessel imo]

Table 2

Transfer Date 

Vessel IMO

I want to set the range for sales dates depending on the transfer date. 

If the transfer occured in 2016, I want to sum only sales from the transfer date to the end of 2016

If the transfer occured in 2017, I want to sum only sales in 2016 prior to the transfer date

I've tried this formula: 

Measure = switch(true(),DATESBETWEEN(Transfers[Transfer Date],"1-Jan-2016","1-Dec-2016"),calculate([Spend USD],DATESBETWEEN(Main[Period],max(Transfers[Transfer Date]),"1-Dec-2016")), datesbetween(transfers[transfer date]),"1-Jan-2017","1-Dec-2017",calculate([spend usd],datesbetween(main[period],"1-Jan-2016",max(transfers[transfer date)])

I get the error: "switch does not support comparing values of type true/false with values of type date." 

[v-huizhn-msft · Accepted solution]

Hi @dniedrauer,

Please try the following fomula and check if it works fine.

Measure = IF((Transfers[Transfer Date]>=DATE(2016,1,1)&&Transfers[Transfer Date]<=DATE(2016,12,31)),CALCULATE([Spend USD],FILTER(Main, Main[Period]>=max(Transfers[Transfer Date])&& Main[Period]<= DATE(2016,12,31))), IF(Transfers[Transfer Date]>=DATE(2017,1,1)&& Transfers[Transfer Date]<=DATE(2017,12,31),CALCULATE([Spend USD],FILTER(Main, Main[Period]>= DATE(2016,1,1))&& Main[Period]<= max(transfers[transfer date))),0))

Best Regards,
Angelia

[dniedrauer]

That works. Thank you.