working with two date fields in a table

BredAnderson

New Member
Joined
Feb 19, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. 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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top