QTD MTD Incorrect result

[amol0512]

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

[amol0512 · Accepted solution]

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)

[amol0512 · Accepted solution]

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 · Accepted solution]

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 )

[Anonymous]

Hi ,

I used this formula but as soon as i change the year to 2021,2020 or 2019 the Value goes to zero.

Posting my formula i may have misconcept it ,

please help!!

CY MTD =

Var MTDTotal =

CALCULATE([Total sale],

DATESMTD( TREATAS( {TODAY()} ,'Main Sheet'[Order Date] ))

)

Return

COALESCE(MTDTotal,0)

[amol0512]

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

[OwenAuger]

Hi Amol,

You're welcome 🙂

With this new measure, just confirming, you want it to calculate relative to "today" don't you?

Are you wanting to display it on a card or in a visual with dates?

To at least make it "relative to today" and convert blank to zero, you could try:

Sample Erros 3 months =
VAR ReferenceDate =
    TODAY ()
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
    COALESCE (
        Result,
        0
    )

All I have done is make ReferenceDate equal to TODAY(), and used COALESCE at the final step to return zero if Result is blank.

Regards,

Owen

[amol0512]

Hi Owen,

I have all cards releative to Today() now. Let's see if Ops has requirement to make it relative to month or Year selection.

This works for me except if we add COALESCE() function then all months are visible from last 3 months. I have removed this function. How we can restrict this?

Regards,

Amol

[OwenAuger]

Hi again,

Just coming back to this one - busy week!

I didn't quite understand the issue - could you illustrate how the visual currently appears and how you want it to appear?

[OwenAuger]

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