Forum Discussion

sarahyyg2's avatar
sarahyyg2
Frequent Visitor
1 year ago
Solved

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

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
PhilipTreacy
Super User
1 year ago

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's avatar
sarahyyg2
Frequent Visitor
1 year ago

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