0
$\begingroup$

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

if(month(startdate)<=month(date),
  if(month(closedate)>month(date),1,0),0)

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

$\endgroup$
1
  • $\begingroup$ Hi @RudyGem, and welcome! I have reformatted your question quite a bit, but imho. it is still a little unclear what you are doing. Could you go over your text one more time, to pinpoint your exact problem? $\endgroup$ Feb 20 '20 at 8:36
0
$\begingroup$

I used this post to learn how to model something similar in PowerBI/DAX. It summarizes how to use generate to create a calculated table that has one row per date and order that shows the status. When the users creates relationships to other tables (dates and statuses in this example) then it’s relatively straightforward to get information about number of open orders or some other information you included in the calculated table (I used contract price in my set up).

$\endgroup$
1
  • $\begingroup$ Please summarize the contents of the link. Link-only answers are discouraged. $\endgroup$
    – oW_
    Apr 12 '20 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.