Cannot compare date to datetime, and cannot convert date/datetime in DAX

[sarahyyg2]

Hi,

I have created a date table with DAX, and all the date values are of date format. I also created columns to store the start of month and end of month dates.

In my fact table 'Tickets' there is a column called [dateReported], also in date format. I want to filter the [dateReported] column with a slicer which selects dates in "MMMM YY" formats. 

I wrote a measure to find the first day of the selected value of the slicer as follows:

First Day of Selected Month =
VAR selectedMonth = SELECTEDVALUE('Date'[Year Month Full])
RETURN CALCULATE(MAX('Date'[Month Start]), FILTER('Date', 'Date'[Year Month Full] = selectedMonth))

This does not work quite as expected as it returns in datetime format when shown in a card visual, which I cannot figure out why:

And I wrote another measure to compare [dateReported] to the [First Day of Selected Month] measure:

Items before Selected Month =
CALCULATE(
    COUNTROWS('Tickets'),
    FILTER(ALL('Tickets'), 'Tickets'[dateReported] < 'Tickets'[First Day of Selected Month])
)

However this gives me BLANK.

I have tried to compare [dateReported] to a date literal and it works as expected:

Items before Selected Month =
CALCULATE(
    COUNTROWS('Tickets'),
    FILTER(ALL('Tickets'), 'Tickets'[dateReported] < dt"2024-10-1")
)

Is there anyway to convert the date/datetime variables so that they can compare to each other?

[sarahyyg2]

@PhilipTreacy 

Thank you for the reply. I worked around it by using hierachy and now it's able to pass the year and month as integers separtely so I can compare. I wrote a DAX measure, and wondering if there is a better way of rewriting this?

Overdue before Selected Month = 
VAR selectedYear = SELECTEDVALUE('Date'[Year])
VAR selectedMonth = SELECTEDVALUE('Date'[Month])
VAR monthNumber = CALCULATE(MAX('Date'[Month Number]), FILTER('Date', 'Date'[Month] = selectedMonth))
RETURN CALCULATE(
    COUNTROWS('Tickets'),
    FILTER(ALL('Tickets'), CONVERT(YEAR('Tickets'[dateReported]), INTEGER) = CONVERT(selectedYear, INTEGER)),
    FILTER(ALL('Tickets'), CONVERT(MONTH('Tickets'[dateReported]),INTEGER) < CONVERT(monthNumber, INTEGER)),
    FILTER(ALL('Tickets'),'Tickets'[overdue] = "Overdue"),
    FILTER(ALL('Tickets'),'Tickets'[status] IN {"New", "Open", "Pending"})
) 
+ CALCULATE(
    COUNTROWS('Tickets'),
    FILTER(ALL('Tickets'), CONVERT(YEAR('Tickets'[dateReported]), INTEGER) < CONVERT(selectedYear, INTEGER)),
    FILTER(ALL('Tickets'),'Tickets'[overdue] = "Overdue"),
    FILTER(ALL('Tickets'),'Tickets'[status] IN {"New", "Open", "Pending"})
) 
+ 0

[Anonymous · Accepted solution]

Hi All,
Firslty  PhilipTreacy  thank you for your solution!

And @sarahyyg2 ,It's great to see your solution to your problem and we try to optimize your code:

Overdue before Selected Month = 
VAR selectedYear = SELECTEDVALUE('Date'[Year])
VAR selectedMonth = SELECTEDVALUE('Date'[Month])
VAR selectedDate = DATE(selectedYear, selectedMonth, 1) -- First day of the selected month
RETURN
    CALCULATE(
        COUNTROWS('Tickets'),
        FILTER(
            ALL('Tickets'),
            'Tickets'[dateReported] < selectedDate &&
            'Tickets'[overdue] = "Overdue" &&
            'Tickets'[status] IN {"New", "Open", "Pending"}
        )
    )

If you have further questions, check out the pbix file I uploaded, I hope it helps and I'd be honored if I could solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

@sarahyyg2 

Can you provide your file?  It'll be much easier to debug with the data and see what it is you are working with.

Regards

Phil

[sarahyyg2]

Thank you for the reply. I have worked around it by using hierachy to pass the number of year and month as integers to compare. The DAX I wrote for the measure is as below, I wonder if there is a better way to rewrite it?

Overdue before Selected Month = 
VAR selectedYear = SELECTEDVALUE('Date'[Year])
VAR selectedMonth = SELECTEDVALUE('Date'[Month])
VAR monthNumber = CALCULATE(MAX('Date'[Month Number]), FILTER('Date', 'Date'[Month] = selectedMonth))
RETURN CALCULATE(
    COUNTROWS('Tickets'),
    FILTER(ALL('Tickets'), CONVERT(YEAR('Tickets'[dateReported]), INTEGER) = CONVERT(selectedYear, INTEGER)),
    FILTER(ALL('Tickets'), CONVERT(MONTH('Tickets'[dateReported]),INTEGER) < CONVERT(monthNumber, INTEGER)),
    FILTER(ALL('Tickets'),'Tickets'[overdue] = "Overdue"),
    FILTER(ALL('Tickets'),'Tickets'[status] IN {"New", "Open", "Pending"})
)
+ CALCULATE(
    COUNTROWS('Tickets'),
    FILTER(ALL('Tickets'), CONVERT(YEAR('Tickets'[dateReported]), INTEGER) < CONVERT(selectedYear, INTEGER)),
    FILTER(ALL('Tickets'),'Tickets'[overdue] = "Overdue"),
    FILTER(ALL('Tickets'),'Tickets'[status] IN {"New", "Open", "Pending"})
) 
+ 0

[sarahyyg2]

Thanks for the reply.

I have figured it out by using hierachy and converting the year and month to integers separately. The DAX I wrote is as follows, but it has to evaluate the same filters twice I suppose. Is there a better way to rewrite this?

Overdue over One Month =
VAR selectedYear = SELECTEDVALUE('Date'[Year])
VAR selectedMonth = SELECTEDVALUE('Date'[Month])
VAR monthNumber = CALCULATE(MAX('Date'[Month Number]), FILTER('Date', 'Date'[Month] = selectedMonth))
RETURN CALCULATE(
    COUNTROWS('Tickets'),
    FILTER(ALL('Tickets'), CONVERT(YEAR('Tickets'[dateReported]), INTEGER) = CONVERT(selectedYear, INTEGER)),
    FILTER(ALL('Tickets'), CONVERT(MONTH('Tickets'[dateReported]),INTEGER) < CONVERT(monthNumber, INTEGER)),
    FILTER(ALL('Tickets'),'Tickets'[overdue] = "Overdue"),
    FILTER(ALL('Tickets'),'Tickets'[status] IN {"New", "Open", "Pending"})
) + 
CALCULATE(
    COUNTROWS('Tickets'),
    FILTER(ALL('Tickets'), CONVERT(YEAR('Tickets'[dateReported]), INTEGER) < CONVERT(selectedYear, INTEGER)),
    FILTER(ALL('Tickets'),'Tickets'[overdue] = "Overdue"),
    FILTER(ALL('Tickets'),'Tickets'[status] IN {"New", "Open", "Pending"})
)