Power BI :: TOTALMTD & DATESMTD are returning Blank

GOAL: the finance office is asking for a dashboard where they could check the MTD expenses. This should be easy, right?

MY APPROACH: there are several guides online that tells you how to calculate the MTD and my approach is this:

MTD = TOTALMTD(CALCULATE(SUM(Data[Cost])),DATESMTD('Calendar'[Date].[Date]))

But when I go to check the results I have 3 problems:

• the line chart is not showing the MTD but is taking all the dates since the beginning
• the line goes up and down and this makes no sense.
• the card underneath is sowing blank

What am I doing wrong?

You can download the here the:

• .pbix
• .xlsx

Thank you

I checked your Calendar table and the maximum date is the 01st of July 2023 :

While in your Data table is the 30th of June 2023 :

I understood that if today is the the 03 rd of July 2023, the measure (in the line chart) should return the result based from today's default date and one month backward :

I assumed that the current date is 31/05/2023 since your data doesn't cover the interval mentioned above :

MTD  = 
CALCULATE(
    SUM(Data[Cost]),
    'Calendar'[Date] >= DATE(2023, 5, 31),
    'Calendar'[Date] <= DATE(2023, 6, 30)
)

If you want your measure to point on the current day, after adjusting your data try the following :

TotalCostMTD = 
CALCULATE(
    SUM(Data[Cost]),
    FILTER(
        ALL('Calendar'[Date]),
        'Calendar'[Date] <= TODAY()
    )
)

Update :

I ended up creating two measure one for the current month and the other for the same period last month :

MTest_Current = CALCULATE(
    SUM(Data[Cost]),
     FILTER(
        Data,
        Data[UsageDate] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) &&
        Data[UsageDate] <= TODAY()
    )
)


MTest_Previous = 
CALCULATE(
    SUM(Data[Cost]),
    FILTER(
        Data,
        Data[UsageDate] >= DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1) &&
        Data[UsageDate] <= DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, DAY(TODAY()))
    )
)

I added a calendar table to follow the best practices :

Calendar = 
ADDCOLUMNS (
    CALENDARAUTO(),
    "Calendar Year", YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "Day", DAY ( [Date] ),
    "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
)

And adjusted the relationships like below :

The final output as the OP is expecting :

I used the pattern that Ferrari & Russo use to do running totals. (a bit more wordy, but you can customize it any way you want). Here's my code from your dataset.

    MTD 2 = CALCULATE ( 
                    SUM('Data'[CostUSD] ), 
                              FILTER( ALL('Calendar'), 
                                   'Calendar'[Date] <= MAX( 'Calendar'[Date] ) &&
                                   'Calendar'[Calendar Year] = MAX('Calendar'[Calendar Year]) &&
                                   'Calendar'[Month Number] = MAX('Calendar'[Month Number]) 
                              )
                )

The problem with the MTD measure in a card is that there's no evaluation context for the month. That's why it's blank. (In plainer English, there's no way for the visual to determine what month it should evaluate... and since the month is effectively "blank"/indeterminate, the total is blank too). One way to sanity check a graph like this is to use a matrix visual and put dates on rows, the Amount, and then run total on Values. It should add up if the measure is working.