Forum Discussion
If condition not working for Dates
Hi Everyone, I have two tables 'Sales' & 'Customers'. There is a Date column in Sales table with the name "SalesDate" that I want to add hours based on the Customers locations.
The Customers table has multiple rows containing a specific row for each customer. I want add 8 hours to Sales[SalesDate] if the number of rows in Customers table is = 1 and value is "bunnings" else add 11 hours to "SalesDate".
My formula is below but it is going to else and adding 11 hours. Not sure why it is doing this even though the value of Customers[CusName] is equal to "bunnings" and there is only 1 row. Any help will be greatly appreciated.
DynamicDate =
VAR SalesDate = Sales[SalesDate]
VAR Customers = MAX(Customers[CusName])
VAR NumRows =
CALCULATE(
COUNTROWS(Customers),
Customers[Name] = "bunnings"
)
VAR AWST = TIME(8, 0, 0)
VAR AEST = TIME(11, 0, 0)
RETURN
IF(
NumRows = 1 && Customers = "bunnings",
SalesDate + AWST,
SalesDate + AEST
)
bIearner83 , a new column in the sale table
= if(related(customer[Name]) = "bunnings" , [sales date] + time(8,0,0) , [Sales Date])
5 Replies
- amitchandak
Super User
bIearner83 , a new column in the sale table
= if(related(customer[Name]) = "bunnings" , [sales date] + time(8,0,0) , [Sales Date])
Thank you. That worked. Thanks to you and amitchandak as well.
- Ahmedx
is your formula a measure or calculated column?
I think you have a problem here:
DynamicDate =
VAR SalesDate = Sales[SalesDate]
VAR Customers = MAX(Customers[CusName])Ahmedx thanks for your reply. Yes, it's a calculated column. for some reason, the Customers = MAX(Customers[CusName]) is not working. I have tested the value of Customers like below and it's only returns a fixed value of the first customer. When apply the filter and using table, it's shows the exact value which is filtered out but here it's not working.
DynamicDate =VAR Customers = MAX(Customers[CusName])RETURNCustomers- Ahmedx
Share some data to work with (in a format the can be pasted in an MS Excel file) and show the expected result.
