Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Date difference between measure and table column

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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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.