I am working with dates.
My fields are in order table:
- Start date
- close date
- order no.
I have to show open orders each month.
I have created a relationship with the date table by start date. I need to relations like:
- If the start date is in this month and before this month and the end date is this month means the order is closed in this month itself it should count 0
- If the start date is in this month or before this month and close in the month after this month it should count 1
Now I have created measure like
It assigns 1 for the condition and then I use some of all ones in visual for every month. Now this works fine with a current start date but for orders with a start date before this month it's not showing the data as start date connected to a date field in the calendar table when I put this in visual. It not showing the start date for previous months. For example: If an order having a start date in the 7th month and an end date in the 9th month it should be in, 7th month and as well as in the 8th month. Right now it's showing only in the 7th month just because of the relationship, I guess.
Also, it's not evaluating the year changes. like it's not capable of comparing data for December '19 and January '20