DatesMTD showing wrong calculation

[Anonymous]

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.  

[Samarth_18]

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]

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

[Samarth_18]

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

[Anonymous]

That's working. Can you tell me how the calcualtion would be for QTD and YTD?

[Samarth_18 · Accepted solution]

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

[Anonymous]

Took some time to reply as I got pulled into a meeting but yes this works! Thankns!

[Greg_Deckler]

@Anonymous Hard to say with the information provided. But I wouldn't use TI functions.

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

[Anonymous]

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]

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