Value summation by date using new measure: DATESMTD?

[Tallone]

I am looking to cacluate a new measure that gives me the sum of the month to date tonnages of the current parameter "CoalTonnes" I cannot seem to get the syntax correct.

[Anonymous · Accepted solution]

DATESMTD returns a set of dates, not a total for month to date.  Try something like:

MTDTonnes = TOTALMTD(SUM('Coal-Day'[CoalTonnes]),'Coal-Day'[Date])

[Tallone]

Thanks for the suggestion Steve, but that formula returns the same as original CoalTonnes field without any summarisation.

[Anonymous]

It looks like you've created it as a Column rather than a Measure, with your screenshot of the Data View?

Try creating it as a Measure instead, and display it via a Table, Chart or other visual.

[Tallone]

Thank you sir, I shall do just that.

[Tallone]

Steve,

I did as you suggested, but as I feared, it made no difference. It still presents the exact same number as the indeividual day. In the charts below, whichever dataset you plot last represents the colour of the line. As there is only one line, but two data sets, they must be representing the same thing.

MTD chart with two datasetsMTD as a measure

[Anonymous]

@Tallone,

Sorry, it shouldn't be this hard...

I can't see why you've got a range of 0-25 on your X axis when you're using the Date column.  Can you load a sample PBIX to OneDrive etc. and post a link so I can check further ?

Cheers,

Steve.

[Tallone]

Hey Steve,

You are seeing integers because currently I'm only displaying the "day" from the date in the x axis. I would have liked to see more than one in every 5 and it was my attempt to save space. To tell you the truth mate, at this point it isnt that huge of a drama. I was trying to make it "slick" in the program so it couild handle variations in the data stream, but for the purposes I am using now I can simply build the MTD values into a dataset in a spreadsheet.

I'm definitely going to revisit this at a later time however opnce the datasets get larger.

Cheers,

Mark

[Anonymous]

Mark,

That explains it - TOTALMTD expects a Date data type, not an integer, across which to total.

Good luck.

[Tallone]

Hi Steve,

The formula is using the entire date field. I only set the axis to view a single day to avoid congestion. thank you very much for your help.

It is greatly appreeciated!

Cheers,

Mark