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 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.
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]))

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    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
    Super 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.