Date difference between measure and table column

[Anonymous]

Hi,

I have a report in which I have created one measure (completed date) with following DAX formula:

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

Now, I have another column which contains Started Date and I want to subtract measure - Started Date, I have tried with following formula:

Measure 2 = DATEDIFF(FORMAT([Measure],"dd/MM/YYYY"), SELECTEDVALUE(G6_SampleData[Started Date].[Date]),DAY)

The reason I have used FORMAT is I am getting an error which states that measure contains "" string. Also when I add measure 2 in my table some rows data gets disappeared. Can anyone please help me with Measure 2 DAX formula ?

Thanks.

[amitchandak · Accepted solution]

@Anonymous , change measure like and  change its data type to date

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

[amitchandak]

@Anonymous , You can get it like this

datediff(max(G6_SampleData[Started Date]), [Measure], DAY)

But you have force a row context using summarize or values example //assume JOB ID

Sumx(values(G6_SampleData[JOB ID]), datediff(max(G6_SampleData[Started Date]), [Measure], DAY))

Averagex(values(G6_SampleData[JOB ID]), datediff(max(G6_SampleData[Started Date]), [Measure], DAY))

refer

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

[Anonymous]

Thanks Amit,

But when I use your provided DAX formula it gives me an error that cannot convert string to Date.

Even if use format it gives me the same error.