Is it possible to calculate difference between a dynamic date and a static date

[Anonymous]

Hi All,

I have a column called entry date in Table A and created a date table called DateDim having column Date. 

When I'm creating the below measure I'm getting error.

Measure 2 = DATEDIFF(('Process Detail'[entry_date]),'DateDim1'[Date],DAY)

Error:

A single value for column 'Date' in table 'DateDim1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

So I tried to create it as a column with same logic but now I'm ending up with below error.

Error:

A single value for column 'entry_date' in table 'Process Detail' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I have created the join betwwen the two tables A and DateDim. Can anyone help me how to acheive this.

Note:

I'm using the Date column from Datedim as a slicer and I need to calculate difference between days of entry date and the selected date(Slicer from datedim)

Thanks,

Anand

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description, you want to get the date diff between the max date of your selected date and the 'Process Detail'[entry_date], you can try my method:

1.      You should delete the relationship between the two tables because the Slicer will affect the value displayed in the table chart if they have a relationship:

2.      Then you can create this measure:

Datediff =

var _selecteddate=MAXX(ALLSELECTED('DateDim'),'DateDim'[Date])

return

DATEDIFF(MAX('Process Detail'[entry_date]),_selecteddate,DAY)

3.      Then place this measure into the table chart, you can get what you want, like this:

And You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , you need to use aggregation and row context

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