Date difference in Report between measure and table column

[Anonymous]

Hi,

I have following to date column in report, Completed Date is a measure and the started date is the earliest started date from table.

Now I want to subtract them in a way that if completed is blank then now()-started date else completed - started date.

The problem is when I display days worked measure my all table data gets disappeared, and I am not sure why.

Following is my DAX Command for Measure Days Worked :

Days Worked = if(ISBLANK([Measure]),DATEDIFF(NOW(),Max(G6_SampleData[Started Date]),DAY),datediff([Measure],Max(G6_SampleData[Started Date]), DAY))

And Following is my DAX Command for Measure Completed Date:

Measure = IF(CALCULATE(COUNTROWS(SampleData),FILTER(SampleData,SampleData[Job Completion Date]=BLANK())) > 0, BLANK(), MAX(SampleData[Job Completion Date]))

Please let me know how can I resolve this issue. Thanks

[amitchandak · Accepted solution]

@Anonymous , Try like

Days Worked =
var _1 = IF(ISBLANK([Measure]),BLANK(),DATEDIFF(MIN(SampleData[Started Date]),[Measure],DAY))
return
if(isblank(_1) , DATEDIFF(MIN(SampleData[Started Date]),now(),DAY),_1)

[amitchandak]

@Anonymous , if they are from same tbale you can create a new column

new column = datediff(coalesce([Job Completion Date], now()),[Started Date],day)

if you need a measure of they are from different table, You need a common context like job id, then you can create a meausre
New measure = sumx(values(JOB[JOB ID]),datediff(coalesce(max([Job Completion Date]), now()),min([Started Date]),day))
New measure = AverageX(values(JOB[JOB ID]),datediff(coalesce(max([Job Completion Date]), now()),min([Started Date]),day))

[Anonymous]

Hi, 

The measure you provided didn't work for me. 

However I have wrote the below DAX in measure and it is working:

Days Worked = IF(ISBLANK([Measure]),BLANK(),DATEDIFF(MIN(SampleData[Started Date]),[Measure],DAY))

Now the olny problem is where I have BLANK(), I want to replace that with now - SampleData[Started Date] but when I do that the data again gets disappeared and wrong values displays in Days Worked Column.