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 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?
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.

5 Replies

  • 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

      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"})
      )
    • 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

       

  • 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.