Forum Discussion
Graph multiple measure that use different dates???
I have a fact table that has rows for service tickets. The table includes collumns for Date Requested, Date Created, Date Finished, and Status (there are others, but they are not important for the issue at hand). Currently, the Fact table is connected to a date table using the Date Requested Field.
I created 2 measures:
# of Tickets Created = Countrows(FactTBL)
# of Completed Tickets = Calculate(Countrows(FactTBL),Status="Complete")
I need to plot the # of tickets created and the # of tickets completed by Month. But, i need the number completed to be based on when they were completed and the number created based on when they were created. Currently, the chart is showing the values based on the Date Requested field.
ex.
| ID | Date Requested | Date Created | Date Finished | Status |
| 1 | 1/1/2016 | 1/5/2016 | 2/1/2016 | Complete |
| 2 | 2/1/2016 | 2/1/2016 | 3/15/2016 | Complete |
| 3 | 2/1/2016 | 2/8/2016 | Open | |
| 4 | 3/1/2016 | 3/5/2016 | 4/10/2016 | Complete |
based on the example above should show for Jan 1 created an 0 completed, for Feb. 2 created and 1 completed, for Mar 1 created and 1 complete. and for April 0 created and 1 complete. That's not what i am getting. Any suggestions on how to resolve this?
Thank you for your assistance. The formula you provided didn't work for me. However, I was able to find a soltuion. I created an inactive relationships for the date fields in my fact table not currently connected to the date table. I wrote the measures that I needed and then wrote another measure using =USERELATIONSHIP. Worked like a charm
2 Replies
Hi erhodes,
Based on your description, you want to let the measure work on each row, right?
If it is a case, you can refer to below code:
Test table:
# of Completed Tickets =
var temp= Calculate(Countrows(TestTable),FILTER(ALL(TestTable),COUNTX(FILTER(TestTable, [Status]="Complete"&& MONTH( TestTable[Date Created])=MONTH( EARLIER(TestTable[Date Finished]))),TestTable[Date Finished])))
return
if(temp>0,temp,0)
# of Tickets Created =
var temp= Calculate(Countrows(TestTable),FILTER(ALL(TestTable),COUNTX(FILTER(TestTable, MONTH( TestTable[Date Created])=MONTH( EARLIER(TestTable[Date Created]))),TestTable[Date Created])))
return
if(temp>0,temp,0)
Result:
Notice: ‘# of Tickets Created’ measure displays the total tickets created in the same month.
Regards,
Xiaoxin Sheng
Thank you for your assistance. The formula you provided didn't work for me. However, I was able to find a soltuion. I created an inactive relationships for the date fields in my fact table not currently connected to the date table. I wrote the measures that I needed and then wrote another measure using =USERELATIONSHIP. Worked like a charm
