CALCULATE measure with date filter

[Anonymous]

Hello! 

My data set: 

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

Table:

Product_ID	Date_Start	...
2432	20.02.2022
3522	21.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.

[Jos_Woolley · Accepted solution]

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

[Jos_Woolley · Accepted solution]

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 · Accepted solution]

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. 

[Anonymous]

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

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

[Jos_Woolley]

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]

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

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