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

     

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