Unable to get same date format for two dates.

[Anonymous]

Hi,

I have two tables which has a date column in each of them. T1[Rush_Date] and T2[Calendar_Date].

Columns:

T1[Rush_Date] = ({7/2/2018 7:58:00 AM;      8/5/2018 4:50:00 PM;       5/5/2018 4:45:45 Am,.........................})

T2[Calendar_Date]= ({Saturday, January 1, 2018;    Sunday, September 5, 2018;       Tuesday, November 7, 2018      ;............ })

I want count of Rush_date according to the selected date in Calendar Date.

I have changed date type and format of both the columns to Date and Format: 3/14/2001 (M/d/yyyy) through Modelling tab.

But still when I select any date, I am seeing 0 value for Rush_Date.

I appreciate any help on this.

Thanks,

Shikha

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

I made one sample for your reference. Here we can create a measure to achieve your goal.

Measure = var mindate = CALCULATE(MIN('T2'[Date]),ALLSELECTED(T2[Date]))
var maxdate = CALCULATE(MAX('T2'[Date]),ALLSELECTED(T2[Date]))
return
CALCULATE(COUNT(Table1[Rush_Date]),FILTER(ALL(Table1),mindate<=Table1[Rush_Date] && maxdate>=Table1[Rush_Date]))

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

I made one sample for your reference. Here we can create a measure to achieve your goal.

Measure = var mindate = CALCULATE(MIN('T2'[Date]),ALLSELECTED(T2[Date]))
var maxdate = CALCULATE(MAX('T2'[Date]),ALLSELECTED(T2[Date]))
return
CALCULATE(COUNT(Table1[Rush_Date]),FILTER(ALL(Table1),mindate<=Table1[Rush_Date] && maxdate>=Table1[Rush_Date]))

For more details, please check the pbix as attached.

Regards,

Frank

[Anonymous · Accepted solution]

Thanks for your response. I will try your solution in some other requirement :).

For this one, I made a SQL query and pulling data from SQL Server.

I appreciate your efforts, thanks a ton again.

[v-frfei-msft]

Hi @Anonymous,

Kindly mark your answer as a solution to close the case please. Thanks in advance.

Regards,

Frank