Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count on specific date wihtout regarding Time

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.

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

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

      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's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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)