Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

CALCULATE measure with date filter

Hello! 

My data set: 

'Date'[Date] - Default data table, used as report filter 

 

Table:

Product_IDDate_Start...
243220.02.2022 
352221.02.2022 
...... 

 

Task: Count number of distinct Product which started at given date

 

MEASURE1 = CALCULATE(DISTINCTCOUNT(Table[Product_ID]), Table[Date_Start] = MAX('Date'[Date]))
gives empty result for any date
but
MEASURE2= CALCULATE(DISTINCTCOUNT(Table[Product_ID]), Table[Date_Start] = MAX('Date'[Date])-1)
gives good result for any date, but its moved by one day 
 
I tried using solution which i found here on forum wich is: 
MEASURE3= CALCULATE(DISTINCTCOUNT(Table[Product_ID]), FILTER(Table, Table[Date_Start] = MAX('Date'[Date])))
but result is still empty

No error reported in PBI
 
I want to use relative date in raport to present number of products for yesterday, today and tommorow, so i need it to work without this (-1) element. It seems to bo so simple measure, but it gave me headache.
 
CALCULATE
Calculate dates
  • Jos_Woolley's avatar
    Jos_Woolley
    4 years ago

    Sounds like the entries in 'Date'[Date] aren't formatted as proper dates. Hence the conversion to a correct date by a simple mathematical operation (for example, your subtraction of 1).

    Regards

  • Anonymous's avatar
    Anonymous
    4 years ago

    Ok, so everything looked fine at first glance. 
    But changing date table from defined in report by:

    Date = CALENDAR(DATE(1990, 01,01),DATE(2099,12,31))

    to Data table generated in power query solved the issue.

    Thanks for guidance. 

5 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Sounds like the entries in 'Date'[Date] aren't formatted as proper dates. Hence the conversion to a correct date by a simple mathematical operation (for example, your subtraction of 1).

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago
      Date = CALENDAR(DATE(1990, 01,01),DATE(2099,12,31))


      and column: 'Date'[Date], data type is "Date"

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Ok, so everything looked fine at first glance. 
      But changing date table from defined in report by:

      Date = CALENDAR(DATE(1990, 01,01),DATE(2099,12,31))

      to Data table generated in power query solved the issue.

      Thanks for guidance. 

  • Jos_Woolley's avatar
    Jos_Woolley
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi,

    MyMeasure :=
VAR ThisDate =
    MAX( 'Date'[Date] )
RETURN
    CALCULATE(
        DISTINCTCOUNT( 'Table'[Product_ID] ),
        'Table'[Date_Start] = ThisDate
    )

    which is identical to:

    MyMeasure :=
VAR ThisDate =
    MAX( 'Date'[Date] )
RETURN
    CALCULATE(
        DISTINCTCOUNT( 'Table'[Product_ID] ),
        FILTER(
            ALL( 'Table' ),
            'Table'[Date_Start] = ThisDate
        )
    )

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I have already tried this alternative , no change. As in my post, it works with 

       ThisDate =
    MAX( 'Date'[Date] ) - 1