Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DatesMTD showing wrong calculation

I have written this formula which includes DatesMTD. However, it's showing an incorrect calculation.

 

Test = CALCULATE(SUM(GOALS_CONSOLIDATED[MONTHLY_GOAL]), GOALS_CONSOLIDATED[GOAL_NAME] = "TRx (Dispensed) Count Goal", GOALS_CONSOLIDATED[BRAND] = "ABC", DATESMTD(GOALS_CONSOLIDATED[Date]))
 
Instead of showing 3668 as the answer which is the goal for the current month(August), it is showing a value 8041 which is the goal for the month of December. Can anyone let me know where I'm going wrong.  
Need Help
  • Samarth_18's avatar
    Samarth_18
    5 years ago

    For Quarter :- 

    Test =
CALCULATE (
    SUM ( GOALS_CONSOLIDATED[MONTHLY_GOAL] ),
    GOALS_CONSOLIDATED[GOAL_NAME] = "TRx (Dispensed) Count Goal",
    GOALS_CONSOLIDATED[BRAND] = "ABC",
    QUARTER( GOALS_CONSOLIDATED[Date] ) = QUARTER ( NOW () )
)

     

    For year :-

    CALCULATE (
    SUM ( GOALS_CONSOLIDATED[MONTHLY_GOAL] ),
    GOALS_CONSOLIDATED[GOAL_NAME] = "TRx (Dispensed) Count Goal",
    GOALS_CONSOLIDATED[BRAND] = "ABC",
    Year( GOALS_CONSOLIDATED[Date] ) = Year ( NOW () )
)

     

9 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Greg, I went through the link you provided but didn't understand what you wanted to convey. If you have a solution for the month to date, can you write how the formula will be in my case? Thakns

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        In your 2nd link, I see a formula written for YTD but there's no formula for MTD and QTD 😞

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    5 years ago

    Hi Anonymous ,

     

    Do you have calender table/date table which connected to GOALS_CONSOLIDATED table if yes then pass date column from calender/date table into DatesMTD function.

     

    Thank you,

    Samarth

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Tried what you said but it still isn't giving the correct answer

      • Samarth_18's avatar
        Samarth_18
        Community Champion
        5 years ago

        Can you try below code:- 

         

        Test =
CALCULATE (
    SUM ( GOALS_CONSOLIDATED[MONTHLY_GOAL] ),
    GOALS_CONSOLIDATED[GOAL_NAME] = "TRx (Dispensed) Count Goal",
    GOALS_CONSOLIDATED[BRAND] = "ABC",
    MONTH ( GOALS_CONSOLIDATED[Date] ) = MONTH ( NOW () )
)