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

    • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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)