Measure does not work for Month Threshold

Question

I build this Dax measure

_Access_Daily = CALCULATE(
    DISTINCTCOUNTNOBLANK(ApplicationAccessLog[ApplicationUserID]),
FILTER('Date','Date'[DateId]=SELECTEDVALUE('DateSelector'[DateId],MAX('DateSelector'[DateId]))))+0

_Access__PreviousDay = CALCULATE(
DISTINCTCOUNTNOBLANK(ApplicationAccessLog[ApplicationUserID]),  FILTER('Date','Date'[DateId]=SELECTEDVALUE('DateSelector'[DateId],MAX('DateSelector'[DateId]))-1 ))+0

The Date Selector table is a disconnected table containing dates from the 20th Jan to now. Dateid is a whole number like 20200131. The Date table is a standard date table with all the dates between 1970 and 2038. Date id is a whole number like 20200131. However it does not seems to work for the month threshold between Jan and Feb ? So if selected date is 01/02/2020 then it does not return correctly for the 31/01/2020.

you should use regular dates instead the whole numbers. If you subtract 1 from 20200201, you will get 20200200. It's a legitimate number, but not a legitimate date — RADO
Cheers for that insight . Helped me think to crack it . Solution is this filter , FILTER('Date','Date'[DayDateNext]=SELECTEDVALUE('DateSelector'[DayDate],MAX('DateSelector'[DayDate])) ))+0 — James Khan

RADO RADO · Accepted Answer · 2020-02-16T20:32:30

As mentioned in the comments, the root problem here is that the whole numbers you use are not dates. As a result, when you subtract 1 and cross month (or year) boundaries, there is no calendar intelligence that can adjust the numbers properly.

Your solution (using 'Date'[DayDateNext]) might work, and if for some additional considerations this design is a must, go with it. However, I'd suggest to revisit the overall approach and use real dates instead of "DateId". You will then be able to use built-in DAX time intelligence, and your code will be more elegant and faster.

For example, if your "Date" and "DateSelector" tables have regular date fields, your code can be re-written as follows:

_Access_Daily =
VAR Selected_Date = SELECTEDVALUE ( 'DateSelector'[Date], MAX ( 'DateSelector'[Date] ) )
VAR Result =
    CALCULATE (
        DISTINCTCOUNTNOBLANK ( ApplicationAccessLog[ApplicationUserID] ),
        'Date'[Date] = Selected_Date
    )
RETURN
    Result + 0

and:

_Access_PreviousDay =
CALCULATE ( [_Access_Daily], PREVIOUSDAY ( 'Date'[Date] ) )

Measure does not work for Month Threshold

1 Answers