Count on specific date wihtout regarding Time

[Anonymous]

I have this measures:

Count Dispatch Blank = 
VAR td = Max(DimDate[Date])

Return
CALCULATE(Count(CD_OTTC_SHIPPING_LINE[KeyID]);CD_OTTC_SHIPPING[ISBLANK_DISPATCH_DATE] = TRUE;DimDate[Date] =td)

This works fine until i have dates with other times than 00:00 in my source data ("Dispatch Date"). For example i have

- 2018-12-10 00:00

- 2018-12-10 12:00

In that case my measure only counts one since "Dispatch date" always has to be equal to "DimDate".

Any suggestion how to solve this.

[v-juanli-msft]

Hi @Anonymous 

I'm not clear about these:

Relationships between "CD_OTTC_SHIPPING_LINE", "CD_OTTC_SHIPPING", "DimDate".

Could you give me some data for example?

It is better to show your original data and expected result.

Best Regards
Maggie

[Anonymous]

I just need a way to not regard the time on the dates. My measure should count 19 for 2018-12-10 but it only counts 17 since 2 of the values are not equal to my DimDate which is 2018-12-10 00:00.

New Code:

Count Dispatch Blank = 
VAR td = max(DimDate[Date])

Return
CALCULATE(Count(CD_OTTC_SHIPPING_LINE[KeyID]);CD_OTTC_SHIPPING_LINE[ISBLANK_DISPATCH_DATE] = TRUE;CD_OTTC_SHIPPING[EARLY_SHPDTE] <= td)

[Anonymous]

Sorry this is the code:

Below is the table where i count [KeyID]. I made a calender that is relatet to the table. For the Report i need a table that contains each day and the Count of [KeyID] that has "true" in [ISBLANK_DISPATCH_DATE]. Which works sort of but it should be 19 instead of 17. I think if you look at the images it will be clear. Since my DimDate has to be equal to [EARLY_SHPDTE] it does not count the 12:00-ones DimDate is allways 00:00. It does not count the last 2 yellow marked ones!

Count Dispatch Blank = 
VAR td = Max(DimDate[Date])

Return
CALCULATE(Count(CD_OTTC_SHIPPING_LINE[KeyID]);CD_OTTC_SHIPPING_LINE[ISBLANK_DISPATCH_DATE] = CD_OTTC_SHIPPING[EARLY_SHPDTE] =td)

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

I can reproduce your problem.

To slove this, i create a column in my Sheet4,(CD_OTTC_SHIPPING_LINE on your side)

date.date = [date].[Date]

Then create relationships

create a measure

Measure = CALCULATE(COUNT(Sheet4[key id]),Sheet4[isblank_date]="true")

I think your measure is also ok, you could try yours first, if it throw some error or unexpected result, please show me here.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.