BredAnderson
New Member
- Joined
- Feb 19, 2020
- Messages
- 1
- Office Version
- 2016
- Platform
- Windows
Hi,
I am working with dates
My fields are in order table ...Start date, close date and order no.
I have to show open orders in each month
I have created relationship with date table by start date
I need to show data like
If start date is in this month and before this month and end date is this month
means the order is closed in this month itself
it should count 0
If start date is in this month or before this month and close in month after this month
it should count 1
Now i have created measure like
if(month(startdate)<=month(date),
if(month(closedate)>month(date),1,0),0)
it assigns 1 for the condition and then I use sum of all ones in visual for every month
now this works fine with current start date but
for order with startdate before this month its not showing the data
as start date connected to date field in calendar table, when i put this in visual
It not showing the start date for previous months
for eg: if an order having start date in 7th month and end date in 9th month it should be in, 7th month and as well as in 8th month
but right now its showjng only jn 7th month just because of relationship i guess
Also, its not evaluating whe year changes. like its not capable for comparing data for December '19 and January '20
I am working with dates
My fields are in order table ...Start date, close date and order no.
I have to show open orders in each month
I have created relationship with date table by start date
I need to show data like
If start date is in this month and before this month and end date is this month
means the order is closed in this month itself
it should count 0
If start date is in this month or before this month and close in month after this month
it should count 1
Now i have created measure like
if(month(startdate)<=month(date),
if(month(closedate)>month(date),1,0),0)
it assigns 1 for the condition and then I use sum of all ones in visual for every month
now this works fine with current start date but
for order with startdate before this month its not showing the data
as start date connected to date field in calendar table, when i put this in visual
It not showing the start date for previous months
for eg: if an order having start date in 7th month and end date in 9th month it should be in, 7th month and as well as in 8th month
but right now its showjng only jn 7th month just because of relationship i guess
Also, its not evaluating whe year changes. like its not capable for comparing data for December '19 and January '20