Forum Discussion

amol0512's avatar
amol0512
Helper I
4 years ago
Solved

QTD MTD Incorrect result

Hi, I am trying to show QTD and MTD numbers in a card on the top. But they are not showing currect result. I have data from Jan 2021 and Jan 2022 and I have 1 error for Jan 2022. so ideally it should show 1 in QTD and 1 in MTD. It shows numbers only if month-Year is selected in a slicer. And amazingly it does not show even if I select year 2022. Whats wrong with it?

 

QTD Erros = if(CALCULATE([Total Errors], DATESQTD('Calendar'[Date]))=Blank(),"0",
CALCULATE([Total Errors], DATESQTD('Calendar'[Date])))
 

 

 

 

Need Help
  • amol0512's avatar
    amol0512
    4 years ago
    Thanks for your help,
    I tried but its giving an error now.
    "Parameter is not the correct type"
     
    Sample QTD Errors =
    Var QTDErrorToday =
    CALCULATE([Total Errors],DATESQTD('Calendar'[Date] =TODAY())
    )
    Return
    COALESCE(QTDErrorToday,0)
  • OwenAuger's avatar
    OwenAuger
    4 years ago

    That's odd - that syntax works for me in a test model.

     

    Does this alternative syntax work, using TREATAS for the filter on today instead of the boolean expression?

    Sample QTD Errors =
VAR QTDErrorToday =
    CALCULATE (
        [Total Errors],
        DATESQTD ( TREATAS ( { TODAY () }, 'Calendar'[Date] ) )
    )
RETURN
    COALESCE ( QTDErrorToday, 0 )

     

8 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    4 years ago

    Hi amol0512 

    The explanation for this behaviour is that the built-in time intelligence functions modify the date filter relative to the dates visible in the current filter context, not relative to the current date..

     

    In the case of MTD and QTD, the period will be defined relative to the maximum date visible.

     

    So if no filters are applied, the date filter applied will be the latest MTD or QTD period existing in your entire 'Calendar' table, which may return no result if your 'Calendar' table extends beyond the date range of your fact table.

     

    However, you can return a result relative to today by applying TODAY() as a date filter.

     

    A couple of other points:

    • It is generally not advisable to replace a blank result with zero for a measure that will be displayed grouped by any other fields. However, it is safe when displaying the measure on a visual such as a card with no grouping.
    • I would recommend replacing with the number 0 rather than "0".

     

    Assuming

    • This measure is to be displayed on a card, so we can keep the BLANK => zero conversion
    • You want the QTD period to be defined relative to the current date

    I would recommend writing the measure like this:

    QTD Erros =
VAR QTDErrosToday =
    CALCULATE (
        [Total Errors],
        DATESQTD ( 'Calendar'[Date] = TODAY () )
    )
RETURN
    COALESCE ( QTDErrosToday, 0 )

    Similarly for MTD.

     

    Does that work for you?

     

    Regards,

    Owen

  • amol0512's avatar
    amol0512
    Helper I
    4 years ago
    Thanks for your help,
    I tried but its giving an error now.
    "Parameter is not the correct type"
     
    Sample QTD Errors =
    Var QTDErrorToday =
    CALCULATE([Total Errors],DATESQTD('Calendar'[Date] =TODAY())
    )
    Return
    COALESCE(QTDErrorToday,0)
    • OwenAuger's avatar
      OwenAuger
      Super User
      4 years ago

      That's odd - that syntax works for me in a test model.

       

      Does this alternative syntax work, using TREATAS for the filter on today instead of the boolean expression?

      Sample QTD Errors =
VAR QTDErrorToday =
    CALCULATE (
        [Total Errors],
        DATESQTD ( TREATAS ( { TODAY () }, 'Calendar'[Date] ) )
    )
RETURN
    COALESCE ( QTDErrorToday, 0 )

       

      • amol0512's avatar
        amol0512
        Helper I
        4 years ago

        Hi Owen,

         

        Yes! that worked well. This was great solution. Thanks for help but now unable to correct this one on same line.

         

        I have Previous Date table which is the duplicate of Calendar table. And this has inactive relationship with Calendar table. This also same problem along with it does not show missing months as zero. 

         

        Sample Erros 3 months =
        VAR ReferenceDate = max('Calendar'[Date])
        VAR PreviousDates =
        DATESINPERIOD(
        'Previous Date'[Date],
        ReferenceDate,
        -3,
        MONTH
        )
        VAR Result =
        CALCULATE(
        [Total Errors],
        REMOVEFILTERS('Calendar'),
        KEEPFILTERS(PreviousDates),
        USERELATIONSHIP('Calendar'[Date],'Previous Date'[Date])
        )
         
        Return
        Result

         

        These DAX are so difficult n there are multiple ways to tackle issues.🤔 

         

        Regards,

        Amol