Graph multiple measure that use different dates???

[erhodes]

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?

[Anonymous]

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

[erhodes · Accepted solution]

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