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.

sarahyyg2's avatar
sarahyyg2
Frequent Visitor
1 year ago

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's avatar
    Anonymous
    Not applicable
    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.