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 )

     

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

OwenAuger's avatar
OwenAuger
Super User
4 years ago

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's avatar
    amol0512
    Helper I
    4 years ago

    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's avatar
      OwenAuger
      Super User
      4 years ago

      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?