'Cannot convert value 'TRUE'of type Text to type Date'

[kristina-brooly]

Please help me with this measure. It gives the error:

'Cannot convert value 'TRUE'of type Text to type Date'

Current period users registrarions =

CALCULATE(COUNT('Users'[ID user]), FILTER('Users, DATEVALUE('Users'[Date of registration]>=MAX('Calendar'[Period begin]))), USERELATIONSHIP('Users'[Date of registration], 'Calendar'[Date]))

I changed data type to date in all participating columns, but still gives this error.

The company has their own periods, which start different date every month (Period begin) and they want to analyze users registrations for each period.

[halfglassdarkly]

Your use of FILTER('Users, DATEVALUE('Users'[Date of registration]>=MAX('Calendar'[Period begin]))) is taking the Boolean result from 'Users'[Date of registration]>=MAX('Calendar'[Period begin]) and trying to convert this to a date, hence the error. 

You shouldn't need to include DATEVALUE() as part of your filter at all.

[johnt75]

The problem is that you have the close bracket for DATEVALUE in the wrong place, it is enclosing the comparison with MAX Calendar period, which is a boolean.

If the columns are all dates there is no need to use DATEVALUE at all, you can use

Current period users registrarions =
CALCULATE (
    COUNT ( 'Users'[ID user] ),
    'Users'[Date of registration] >= MAX ( 'Calendar'[Period begin] ),
    USERELATIONSHIP ( 'Users'[Date of registration], 'Calendar'[Date] )
)

[kristina-brooly]

I tried to do how you wrote, but didn't get the desired result.

I need to calculate amount of users for the current (latest) period, registered after 09.02.2023

Am I doing wrong with dates comparison?

Period table is below:

period	period begin	end date
2022_P9	08.09.2022	12.10.2022
2022_P10	13.10.2022	09.11.2022
2022_P11	10.11.2022	07.12.2022
2022_P12	08.12.2022	11.01.2023
2023_P1	12.01.2023	08.02.2023
2023_P2	09.02.2023

[johnt75]

How is the period table related to the other tables, or is it just columns on the Calendar table ? Does each date in the Calendar table have a period begin and end date ?

[kristina-brooly]

these are just columns in Calendar.

Each date has period begin and the name of a period)))

[johnt75]

Then I don't think you need to specify the filter. If your users table only has 1 row per user you can use

Current period users registrarions =
CALCULATE (
    COUNTROWS ( 'Users' ),
    USERELATIONSHIP ( 'Users'[Date of registration], 'Calendar'[Date] )
)

If there may be duplicates then you would need to change the COUNTROWS back to COUNT, but COUNTROWS is better.

In either case, you should be able to put that measure into a visual with the period start from the calendar table and it will show the number of users for that period.

If you just want the latest figure in a card visual or something, you could just put a filter on the card. Maybe add a column to the calendar table to return 1 if it is the latest period.

[Greg_Deckler]

@kristina-brooly Try:

Current period users registrarions =
CALCULATE(
  COUNT('Users'[ID user]), 
  FILTER('Users, DATEVALUE('Users'[Date of registration]) >= MAX('Calendar'[Period begin])), 
  USERELATIONSHIP('Users'[Date of registration], 'Calendar'[Date]))